使用CASE及COUNT对不同值进行计数
1. SQL Server中
select TeacherNo,(case WeekNo when 1 then count(TeacherNo) end) XQ1,
(case WeekNo when 2 then count(TeacherNo) end) XQ2,
(case WeekNo when 3 then count(TeacherNo) end) XQ3
from A
group by TeacherNo;
2. Oracle 中
with A as (
select 1 TeacherNo,2 WeekNo from dual
union all
select 1 TeacherNo,3 WeekNo from dual
union all
select 2 TeacherNo,1 WeekNo from dual
union all
select 3 TeacherNo,2 WeekNo from dual
union all
select 1 TeacherNo,2 WeekNo from dual
)
select TeacherNo,count(case WeekNo when 1 then 1 else null end) XQ1,
count(case WeekNo when 2 then 1 else null end) XQ2,
count(case WeekNo when 3 then 1 else null end) XQ3
from A
group by TeacherNo;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10314474/viewspace-772386/,如需转载,请注明出处,否则将追究法律责任。