一个关于sql中 case when then 的语法问题

Question

《1》  select case 3
when 1 then 1
when 2 then 2
when 3 then 3
when 3 then 4
end
from test;

《2》  select case 3
when 1 then 1
when 2 then 2
when 3 then 4
when 3 then 3
end
from test;

 

我想问的是 为什么第一条sql语句的结果是3  第二条结果是4呢 百思不得其解

（我想通了 一旦when中匹配case的条件 就会跳出case循环 这个我懂了 下面还一个问题）

 

 

select a.VR_tagID,hr_maindeptid,Hr_staffName ,
(case when datepart(mm,writeTime)=1 then count(a.VR_tagID) else 0 end) Jan,
(case when datepart(mm,writeTime)=2 then count(a.VR_tagID) else 0 end) Feb,
(case when datepart(mm,writeTime)=3 then count(a.VR_tagID) else 0 end) Mar,
(case when datepart(mm,writeTime)=4 then count(a.VR_tagID) else 0 end) Apr,
(case when datepart(mm,writeTime)=5 then count(a.VR_tagID) else 0 end) May
,
(case when datepart(mm,writeTime)=6 then count(a.VR_tagID) else 0 end) June
,
(case when datepart(mm,writeTime)=7 then count(a.VR_tagID) else 0 end) July
,
(case when datepart(mm,writeTime)=8 then count(a.VR_tagID) else 0 end) Aug
,
(case when datepart(mm,writeTime)=9 then count(a.VR_tagID) else 0 end) Sept
,
(case when datepart(mm,writeTime)=10 then count(a.VR_tagID) else 0 end) Oct
,
(case when datepart(mm,writeTime)=11 then count(a.VR_tagID) else 0 end) Nov
,
(case when datepart(mm,writeTime)=12 then count(a.VR_tagID) else 0 end) Dec
,count(a.VR_tagID) Toal

from dbo.VR_WorkCheckDetail a
inner join hr_staff b on a.VR_TagID=b.VR_TagID

group by datepart(mm,writeTime), a.VR_tagID,hr_maindeptid,Hr_staffName

 

 

这个语句 大意就是查询 vr_tagid 在 VR_WorkCheckDetail  一月出现的次数 二月出现的次数  三月...  以此类推  但是我发现,比如只要一月有了一次以上的次数 那么后面所有月份的次数都是0  这是为什么呢 求大神指教！！！

Answer 1

感觉要用子查询, select a.VR_tagID,hr_maindeptid,Hr_staffName ,(select count(a.VR_tagID)  from table where case when datepart(mm,writeTime)=1 ) as  Jan,

Answer 2

select a.VR_tagID,hr_maindeptid,Hr_staffName , max(case when datepart(mm,writeTime)=1 then count(a.VR_tagID) else 0 end) Jan, max(case when datepart(mm,writeTime)=2 then count(a.VR_tagID) else 0 end) Feb, max(case when datepart(mm,writeTime)=3 then count(a.VR_tagID) else 0 end) Mar, max(case when datepart(mm,writeTime)=4 then count(a.VR_tagID) else 0 end) Apr, max(case when datepart(mm,writeTime)=5 then count(a.VR_tagID) else 0 end) May , max(case when datepart(mm,writeTime)=6 then count(a.VR_tagID) else 0 end) June , max(case when datepart(mm,writeTime)=7 then count(a.VR_tagID) else 0 end) July , max(case when datepart(mm,writeTime)=8 then count(a.VR_tagID) else 0 end) Aug , max(case when datepart(mm,writeTime)=9 then count(a.VR_tagID) else 0 end) Sept , max(case when datepart(mm,writeTime)=10 then count(a.VR_tagID) else 0 end) Oct , max(case when datepart(mm,writeTime)=11 then count(a.VR_tagID) else 0 end) Nov , max(case when datepart(mm,writeTime)=12 then count(a.VR_tagID) else 0 end) Dec ,count(a.VR_tagID) Toal from dbo.VR_WorkCheckDetail a inner join hr_staff b on a.VR_TagID=b.VR_TagID group by datepart(mm,writeTime), a.VR_tagID,hr_maindeptid,Hr_staffName   你这个语句有错误。这个应该是行转列的吧？你这样写，会出现你看到的现象，而你的需求肯定不是这样的， 修改： 对每个case 都增加一个MAX聚合：

Answer 3

我尝试你的写法 报错：  不能对包含聚合或子查询的表达式执行聚合函数。

Answer 4

需求其实是这样的  vr_tagid是标识卡列 代表一个人 而 VR_WorkCheckDetail 是记录标识卡每个月出现的次数 现在我就是想统计出 所有人 每个月 出现的次数