关于分组
表: GHB
主要列:LSH ,YS ,HB ,JE
示例数据行:
2013110100001,张医师,初诊,4.00
2013110100002,李医师,初诊,4.00
2013110100003,李医师,复诊,2.00
2013110100004,陈医师,初诊,4.00
………………………
2013111500001,张医师,初诊,4.00
2013110500002,刘医师,复诊,2.00
………………………
2013113000222,朱医师,复诊,2.00
2013113000223,杨医师,初诊,4.00
查询语句:
select YS,HB,COUNT(LSH), SUM(JE) from GHB where LEFT(LSH,6)='201311' group by YS ,HB
我想要的结果是:
怎样写?
[解决办法]
试试这个
select YS,max(case when HB='初诊' then LSH else 0 end) as '初诊',
max(case when HB='复诊' then LSH else 0 end) as '复诊',
sum(JE) as '金额'
from
(select YS,HB,COUNT(LSH)LSH, SUM(JE)JE from GHB where LEFT(LSH,6)='201311' group by YS ,HB)
group by YS