select a.aid,a.na,sum(b.bt),count(b1),count(b2),count(b3),count(b4),count(b5) from A left join b on a.id=b.aid group by a.aid,a.na
select a.aid,a.na,sum(b.bt),count(b1),count(b2),count(b3),count(b4),count(b5) from A left join b on a.id=b.aid group by a.aid,a.na
select a.aid,a.na,sum(isnull(b.bt,0)),count(b1),count(b2),count(b3),count(b4),count(b5) from A ,B where a.id=b.aid group by a.aid,a.na
select na,sum(b.bt), sum(case when b=1 then 1 else 0 end ), sum(case when b=2 then 1 else 0 end ), sum(case when b=3 then 1 else 0), sum(case when b=4 then 1 else 0), sum(case when b=5 then 1 else 0) from a,b where a.id=b.aid group by a.id,a.na
select na as 姓名,sum(b.bt) as 总量
,count(b1) 合计1
,count(b2) 合计2
......
from A left join B on a.id=b.aid
group by a.na
from a join b on a.id=b.aid group by a.na
两个表格A,B。
其中A有id,na,B有id,aid,bt,b俩个表的关系是A.id=B.aid.
要求用一个查询语句实现下列功能。对B.bt进行sum(),b的值分别有1,2,3,4,5
显示的数据如:
na sum(b.bt) count(b1) count(b2) count(b3) count(b4) count(b5)姓名 总量 合计1 合计2 合计3 合计4 合计5谢谢!!
from a,b where a.id=b.aid group by a.id,a.na