表如下
F1 F2 F3
aa 1 1
aa 2 1
aa 3 2
bb 2 1
bb 3 2
cc 4 1
想用一条语句统计出来
aa F2的统计 F3中等于1的合计个数
返回结果如下
F1 F2 F3
aa 6 2
bb 5 1
cc 4 1
------解决方案--------------------------------------------------------
- SQL code
select a.F1,a.F2,b.F3 from (select F1,sum(F2) as F2 from F group by F1) ainner join (Select F1,count(F3) as F3 from F where F3=1 group by F1 ) bon a.F1=b.F1
------解决方案--------------------------------------------------------
create table #tb(f1 varchar(30),f2 int,f3 int)
insert into #tb select 'aa',1,1
union select 'aa',2,1
union select 'aa',3,2
union select 'bb',2,1
union select 'bb',3,2
union select 'cc',4,1
select f1,sum(f2) f2,sum(case f3 when 1 then 1 else 0 end) f3 from #tb group by f1
f1 f2 f3
------------------------------ ----------- -----------
aa 6 2
bb 5 1
cc 4 1
(3 行受影响)