ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL Server、Oracle中CASE 与COUNT合用计数

SQL Server、Oracle中CASE 与COUNT合用计数

原创 Linux操作系统 作者:hky87 时间:2013-09-09 12:05:14 0 删除 编辑
使用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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: BULK COLLECT
下一篇: SQL Server中MD5加密
请登录后发表评论 登录
全部评论

注册时间:2009-10-09

  • 博文量
    87
  • 访问量
    255538