sql case when 较复杂的用法

最新推荐文章于 2025-06-23 16:07:14 发布

原创最新推荐文章于 2025-06-23 16:07:14 发布 · 723 阅读

1 ·

CC 4.0 BY-SA版权

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

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