110,534
社区成员
发帖
与我相关
我的任务
分享
with tb(ID,Morning,Noon,Night)as
(
select 1,'张三','李四','王五' union
select 2,'王五','张三','王五' union
select 3,'张三','王五','张三' union
select 4,'李四','张三','王五' union
select 5,'李四','王五','李四' union
select 6,'张三','李四','王五' union
select 7,'李四','张三','李四' union
select 8,'王五','李四','赵六'
)
select Name,
case when MorningCnt is NULL then '0' else MorningCnt end as MorningCnt,
case when NoonCnt is null then '0' else NoonCnt end as NoonCnt,
case when NightCnt is null then '0' else NightCnt end as NightCnt from
(select Morning as Name from tb union
select Noon as Name from tb union
select Night as Name from tb) as BASE
full join (select Morning,count(Morning) as MorningCnt from tb group by Morning ) as MORNING on BASE.Name = MORNING.Morning
full join (select Noon,count(Noon) as NoonCnt from tb group by Noon) as NOON on BASE.Name = NOON.Noon
full join (select Night,count(Night) as NightCnt from tb group by Night) as NIGHT on BASE.Name = NIGHT.Night
Name MorningCnt NoonCnt NightCnt
李四 3 3 2
王五 2 2 4
张三 3 3 1
赵六 0 0 1
select 3,'张三','王五','张三' union
select 4,'李四','张三','王五' union
select 5,'李四','王五','李四' union
select 6,'张三','李四','王五' union
select 7,'李四','张三','李四' union
select 8,'王五','李四','赵六'
)
select Name,
case when MorningCnt is NULL then '0' else MorningCnt end as MorningCnt,
case when NoonCnt is null then '0' else NoonCnt end as NoonCnt,
case when NightCnt is null then '0' else NightCnt end as NightCnt from
(select Morning as Name from tb union
select Noon as Name from tb union
select Night as Name from tb) as BASE
full join (select Morning,count(Morning) as MorningCnt from tb group by Morning ) as MORNING on BASE.Name = MORNING.Morning
full join (select Noon,count(Noon) as NoonCnt from tb group by Noon) as NOON on BASE.Name = NOON.Noon
full join (select Night,count(Night) as NightCnt from tb group by Night) as NIGHT on BASE.Name = NIGHT.Night
Name MorningCnt NoonCnt NightCnt
李四 3 3 2
王五 2 2 4
张三 3 3 1
赵六 0 0 1