如何获取员工每日首次登录时间并过滤晚于09:45:00的记录

要筛选出每日首次登录时间晚于09:45:00的记录，直接在现有SQL的GROUP BY子句后添加HAVING条件即可——因为我们要过滤的是分组后的聚合结果（每日首次登录时间），WHERE只能处理分组前的原始行，无法作用于计算出的FirstLogin字段。

根据你使用的数据库类型，修改后的SQL如下：

MySQL版本 #

SELECT UserId, employees.EmployeeName as name, MIN(LogDate) as FirstLogin 
FROM devicelogs_processed 
LEFT JOIN employees ON employees.EmployeeCode = devicelogs_processed.UserId 
WHERE '$empId' = UserId AND LogDate BETWEEN '2022-07-01' AND '2022-07-31' 
GROUP BY UserId, YEAR(LogDate), MONTH(LogDate), DAY(LogDate) 
HAVING TIME(FirstLogin) > '09:45:00'
ORDER BY `FirstLogin` DESC

SQL Server版本 #

SELECT UserId, employees.EmployeeName as name, MIN(LogDate) as FirstLogin 
FROM devicelogs_processed 
LEFT JOIN employees ON employees.EmployeeCode = devicelogs_processed.UserId 
WHERE '$empId' = UserId AND LogDate BETWEEN '2022-07-01' AND '2022-07-31' 
GROUP BY UserId, YEAR(LogDate), MONTH(LogDate), DAY(LogDate) 
HAVING CAST(FirstLogin AS TIME) > '09:45:00'
ORDER BY FirstLogin DESC

Oracle版本 #

SELECT UserId, employees.EmployeeName as name, MIN(LogDate) as FirstLogin 
FROM devicelogs_processed 
LEFT JOIN employees ON employees.EmployeeCode = devicelogs_processed.UserId 
WHERE '$empId' = UserId AND LogDate BETWEEN TO_DATE('2022-07-01', 'YYYY-MM-DD') AND TO_DATE('2022-07-31', 'YYYY-MM-DD') 
GROUP BY UserId, EXTRACT(YEAR FROM LogDate), EXTRACT(MONTH FROM LogDate), EXTRACT(DAY FROM LogDate) 
HAVING TO_CHAR(FirstLogin, 'HH24:MI:SS') > '09:45:00'
ORDER BY FirstLogin DESC

关键说明 #

• HAVING子句专门用于分组后的结果过滤，这里通过提取FirstLogin的时间部分与'09:45:00'比较，只保留符合条件的分组。
• 不同数据库提取时间部分的函数有差异：MySQL用TIME()，SQL Server用CAST(... AS TIME)，Oracle用TO_CHAR(..., 'HH24:MI:SS')（用24小时制避免上午/下午的歧义）。

内容的提问来源于stack exchange，提问作者sujith