以Case...When...Then...End分列输出

系统管理员 2022-08-24 04:23 137阅读 0赞

## [以Case...When...Then...End分列输出][Case...When...Then...End] ##

SELECT 日期, 姓名, (CASE 出勤状态 WHEN '白班' THEN '○' END) AS 白班, (CASE 出勤状态 WHEN '夜班' THEN '○' END) AS 夜班, (CASE 出勤状态 WHEN '休息' THEN '○' END) AS 休息, (CASE 出勤状态 WHEN '调休' THEN '○' END) AS 调休, (CASE 出勤状态 WHEN '病假' THEN '○' END) AS 病假, (CASE 出勤状态 WHEN '事假' THEN '○' END) AS 事假, (CASE 出勤状态 WHEN '迟到' THEN '○' END) AS 迟到, (CASE 出勤状态 WHEN '早退' THEN '○' END) AS 早退, (CASE 出勤状态 WHEN '旷工' THEN '○' END) AS 旷工, (CASE 出勤状态 WHEN '其他' THEN '○' END) AS 其他, 部门, 身份证号, id FROM dbo.人事\_考勤表 WHERE (部门 = @部门) AND (日期 = @日期)  
  
![a2.jpg][]  
  
  
  
'按出勤状态统计某部门每天的出勤数据  
SELECT 日期, 部门,  
count(CASE 出勤状态 WHEN '白班' THEN '○' END) AS 白班,  
count(CASE 出勤状态 WHEN '夜班' THEN '○' END) AS 夜班,  
count(CASE 出勤状态 WHEN '休息' THEN '○' END) AS 休息,  
count(CASE 出勤状态 WHEN '调休' THEN '○' END) AS 调休,  
count(CASE 出勤状态 WHEN '病假' THEN '○' END) AS 病假,  
count(CASE 出勤状态 WHEN '事假' THEN '○' END) AS 事假,  
count(CASE 出勤状态 WHEN '迟到' THEN '○' END) AS 迟到,  
count(CASE 出勤状态 WHEN '早退' THEN '○' END) AS 早退,  
count(CASE 出勤状态 WHEN '旷工' THEN '○' END) AS 旷工,  
count(CASE 出勤状态 WHEN '其他' THEN '○' END) AS 其他  
FROM dbo.人事\_考勤表  
where 部门='设备工具科'  
group by 日期,部门  
order by 日期 desc  
  
![aaaa.jpg][]   
  
'按月统计各单位出勤状态  
SELECT     STR(YEAR(日期)) + '-' + LTRIM(STR(MONTH(日期))) AS 日期, 部门, COUNT(CASE 出勤状态 WHEN '白班' THEN '○' END) AS 白班,  
                      COUNT(CASE 出勤状态 WHEN '夜班' THEN '○' END) AS 夜班, COUNT(CASE 出勤状态 WHEN '休息' THEN '○' END) AS 休息,  
                      COUNT(CASE 出勤状态 WHEN '调休' THEN '○' END) AS 调休, COUNT(CASE 出勤状态 WHEN '病假' THEN '○' END) AS 病假,  
                      COUNT(CASE 出勤状态 WHEN '事假' THEN '○' END) AS 事假, COUNT(CASE 出勤状态 WHEN '迟到' THEN '○' END) AS 迟到,  
                      COUNT(CASE 出勤状态 WHEN '早退' THEN '○' END) AS 早退, COUNT(CASE 出勤状态 WHEN '旷工' THEN '○' END) AS 旷工,  
                      COUNT(CASE 出勤状态 WHEN '其他' THEN '○' END) AS 其他  
FROM         dbo.人事\_考勤表  
WHERE     (部门 = @部门) AND (YEAR(日期) = YEAR(@日期))  
GROUP BY STR(YEAR(日期)) + '-' + LTRIM(STR(MONTH(日期))), 部门  
ORDER BY STR(YEAR(日期)) + '-' + LTRIM(STR(MONTH(日期))) DESC

转自： [http://www.cnblogs.com/sonar/archive/2006/09/05/494902.html][Case...When...Then...End]

[Case...When...Then...End]: http://www.cnblogs.com/sonar/archive/2006/09/05/494902.html
[a2.jpg]: /images/20220823/aec2f8bb66f54f4bb0db6e77f7a34fb0.png
[aaaa.jpg]: /images/20220823/a2c7c734cd7240c38d7e154bea0cfc88.png