表如下:
id number value flag
1 1 10 'A'
2 1 10 'A'
3 1 10 'B'
4 1 10 'C'
5 2 20 'A'
6 3 50 'A'
7 3 50 'A'
8 4 20 'B'
9 5 10 'A'
按flag分组,再相加value,但是重复的number只能加一次
结果要输出成这样的
flag flagCount valueSum
'A' 6 90
'B' 2 30
'C' 1 10
------解决方案--------------------
- SQL code
create table tb(id int,number int,value int,flag varchar(10))insert into tb values(1 ,1 ,10 ,'A')insert into tb values(2 ,1 ,10 ,'A')insert into tb values(3 ,1 ,10 ,'B')insert into tb values(4 ,1 ,10 ,'C')insert into tb values(5 ,2 ,20 ,'A')insert into tb values(6 ,3 ,50 ,'A')insert into tb values(7 ,3 ,50 ,'A')insert into tb values(8 ,4 ,20 ,'B')insert into tb values(9 ,5 ,10 ,'A')goselect flag , (select count(1) from tb where flag = t.flag) flagCount, sum(value) valueSum from( select distinct number , value , flag from tb) tgroup by flagdrop table tb/*flag flagCount valueSum ---------- ----------- ----------- A 6 90B 2 30C 1 10（所影响的行数为 3 行）*/
------解决方案--------------------
- SQL code
create table sup(id int, number int, value int, flag char(1))insert into supselect 1, 1, 10, 'A' union allselect 2, 1, 10, 'A' union allselect 3, 1, 10, 'B' union allselect 4, 1, 10, 'C' union allselect 5, 2, 20, 'A' union allselect 6, 3, 50, 'A' union allselect 7, 3, 50, 'A' union allselect 8, 4, 20, 'B' union allselect 9, 5, 10, 'A'select a.flag,a.flagCount,b.valueSumfrom (select t1.flag,count(*) 'flagCount' from sup t1 group by t1.flag) across apply((select sum(value) 'valueSum' from (select distinct flag,number,value from sup) t2 where t2.flag=a.flag)) b flag flagCount valueSum---- ----------- ----------- A 6 90 B 2 30 C 1 10
------解决方案--------------------
- SQL code
declare @T table(id int,number int,value int,flag varchar(1))insert into @Tselect 1,1,10,'A' union allselect 2,1,10,'A' union allselect 3,1,10,'B' union allselect 4,1,10,'C' union allselect 5,2,20,'A' union allselect 6,3,50,'A' union allselect 7,3,50,'A' union allselect 8,4,20,'B' union allselect 9,5,10,'A'select flag,(select count(1) from @T where flag=a.flag) as flagCount ,sum(value) as valueSumfrom (select distinct number,value,flag from @T)a group by flag/*flag flagCount valueSum---- ----------- -----------A 6 90B 2 30C 1 10*/
------解决方案--------------------
- SQL code
select a.flag,a.flagCount,sum(b.value) as valueSumfrom (select flag,count(1) as flagCount from tb group by flag) ajoin (select distinct number,value,flag from tb) bon a.flag=b.flaggroup by a.flag,a.flagCount/**flag flagCount valueSum---------- ----------- -----------A 6 90B 2 30C 1 10(3 行受影响)**/