SQL Server、Oracle中CASE 與COUNT合用計數

使用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/，如需轉載，請註明出處，否則將追究法律責任。