sql case when 较复杂的用法

iteye_524

于 2012-03-14 16:08:46 发布

阅读量703

点赞数

本文链接：https://blog.csdn.net/iteye_524/article/details/82297555

版权

本文介绍了一个复杂的SQL查询案例，该查询旨在统计员工每日上下班情况，并根据特定条件计算出全天和半天的出勤次数。通过多层CASE WHEN结构和聚合函数实现业务需求。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select emp_id,
biz_date,
case when
sum(case when morning =1 and afternoon =1
then 1
else 0
end
)
+
max(case when morning =1 and afternoon =0
then 1
else 0
end
)
+
max(case when morning = 0 and afternoon =1
then 1
else 0
end
)
>=2
then 2
else
sum(case when morning =1 and afternoon =1
then 1
else 0
end)
+
max(case when morning =1 and afternoon =0
then 1
else 0
end)
+
max(case when morning = 0 and afternoon =1
then 1
else 0
end)
end as vsttimes
from
(
SELECT
emp_id,
biz_date,
sr_id,
max(case when convert(varchar(2),intime,108) < 12
then 1
else 0
end) as morning,

max(case when convert(varchar(2),intime,108) >= 12
then 1
else 0
end) as afternoon

FROM RPT_INOUT_STORE WITH (nolock)
WHERE FUNC_CODE='F50S01'
AND BIZ_DATE>= @current_year+'-01-01'
AND BIZ_DATE<= @current_year+'-12-31'
GROUP BY EMP_ID,biz_date,sr_id

) a
group by a.emp_id,a.biz_date