行转列之前SQL
SELECT zhenshi, status, COUNT(1) AS n
FROM [MyPlan]
WHERE 1 = 1
AND leibie = 1
AND [SickID] = 0
AND zhenshi <> '协同诊疗'
AND t >= '2020-06-06 00:00:00'
AND t < '2020-06-08 00:00:00'
GROUP BY zhenshi, status行转列之前的运行结果
------------------------------------------------------------------------------------------------------
行转列之后SQL
select 0
,zhenshi
,sum(case --status
when status!='完成' then n
else 0
end) '预约病人数量'
,sum(case status
when '已来诊' then n
else 0
end) '已来诊'
,sum(case --status
when status!='已来诊' and status!='完成' then n
else 0
end) '未来诊'
from
(
select zhenshi,status,count(1) n
--CASE status WHEN '开启' THEN status ELSE '开启' END,
--CASE status WHEN '已来诊' THEN status ELSE '已来诊' END,
--CASE status WHEN '未来诊' THEN status ELSE '未来诊' END
FROM [MyPlan] where 1=1 and leibie=1 and [SickID] =0 and zhenshi<>'协同诊疗' --and status='已来诊'
--and zhenshi='好孕一组'
and t>='2020-06-06 00:00:00' and t<'2020-06-08 00:00:00'
group by zhenshi,status
)t
where t.zhenshi!=''
group by zhenshi行转列之后的运行结果
这时候再去程序里遍历，就简单显示了。
这里只做行转列。
列转行暂未写-2020.06.07