如何修改带GROUP BY的SQL查询以过滤无IN记录的打卡数据？

嘿，我来帮你搞定这个问题！你现在的SQL查询会返回所有有IN或OUT打卡记录的员工，但你想要只保留那些确实有IN打卡记录的条目，对吧？

原查询SQL #

select empnam as Emp_Name,seccod as DEPT,'' as REMARKS ,MAX(CASE WHEN STAT = 'IN' THEN clktim END)[IN] ,MAX(CASE WHEN STAT = 'OUT' THEN clktim END) OUT 
from (select a.clkid, case when a.devid = '10' then 'IN' else 'OUT' end as STAT , a.clkdat, a.clktim, b.empid, b.empnam, b.seccod, 
row_number() over(partition by clkdat order by dateinsert) as seqnum 
from ClkInf a INNER JOIN EmpInf b ON a.clkid = b.clkid 
where a.clkdat between (select FORMAT(getdate(), 'yyyyMMdd')) and (select FORMAT(getdate() + 1, 'yyyyMMdd')) and devid in (10, 50) ) mak 
GROUP BY clkid,clkdat,empnam,seccod 
order by DEPT ASC

原查询结果 #

NAME DEPT IN OUT 
Peter STP 647 NULL 
Jordan FCW 647 NULL 
Mark EE 724 NULL 
Lebron STP NULL 810 
Kobe STP NULL 813 
Chris TR 729 NULL 
Dray DC 705 NULL

期望结果 #

NAME DEPT IN OUT 
Peter STP 647 NULL 
Jordan FCW 647 NULL 
Mark EE 724 NULL 
Chris TR 729 NULL 
Dray DC 705 NULL

修改方案 #

你只需要在GROUP BY子句之后添加一个HAVING条件，用来过滤掉没有IN记录的分组即可。因为当某个员工没有IN打卡时，MAX(CASE WHEN STAT = 'IN' THEN clktim END)会返回NULL，我们只要排除这些分组就行。

修改后的完整SQL如下：

select empnam as Emp_Name,seccod as DEPT,'' as REMARKS ,MAX(CASE WHEN STAT = 'IN' THEN clktim END)[IN] ,MAX(CASE WHEN STAT = 'OUT' THEN clktim END) OUT 
from (select a.clkid, case when a.devid = '10' then 'IN' else 'OUT' end as STAT , a.clkdat, a.clktim, b.empid, b.empnam, b.seccod, 
row_number() over(partition by clkdat order by dateinsert) as seqnum 
from ClkInf a INNER JOIN EmpInf b ON a.clkid = b.clkid 
where a.clkdat between (select FORMAT(getdate(), 'yyyyMMdd')) and (select FORMAT(getdate() + 1, 'yyyyMMdd')) and devid in (10, 50) ) mak 
GROUP BY clkid,clkdat,empnam,seccod 
-- 新增的HAVING条件，过滤掉无IN记录的分组
HAVING MAX(CASE WHEN STAT = 'IN' THEN clktim END) IS NOT NULL 
order by DEPT ASC

补充说明 #

• HAVING和WHERE的区别：WHERE是在分组前过滤行数据，而HAVING是在分组后过滤分组结果，正好符合我们这里的需求——先按员工和日期分组计算IN/OUT时间，再过滤掉没有IN记录的分组。
• 如果你使用的数据库支持在HAVING中引用SELECT里的别名（比如SQL Server），也可以简化写成HAVING [IN] IS NOT NULL，但用完整的聚合表达式兼容性更好。

内容的提问来源于stack exchange，提问作者mr.jm