34,590
社区成员
发帖
与我相关
我的任务
分享
with cte
as
(SELECT 接诊日期,接诊时段,人次数,'全天' as 期望效果
from Table A
where 接诊时段 in ('上午','下午')
and exists (select 1 from Table where A.接诊日期=接诊日期 and A.接诊时段<>接诊时段 and 接诊时段 in ('上午','下午'))
union all
SELECT 接诊日期,接诊时段,人次数,'半天' as 期望效果
from Table A
where 接诊时段 in ('上午','下午')
and not exists (select 1 from Table where A.接诊日期=接诊日期 and A.接诊时段<>接诊时段 and 接诊时段 in ('上午','下午'))
SELECT 接诊日期,接诊时段,人次数,'其他' as 期望效果
from Table A
where 接诊时段 not in ('上午','下午'))
select 期望效果,SUM(人次数) as 人次数,COUNT(1) as 个数
from cte
group by 期望效果