如何正确使用UNION、GROUP BY与ORDER BY拆分SQL考勤数据

你的查询存在以下核心问题：

1. UNION ALL合并后的数据每行仅包含time_in或time_out单值，外层GROUP BY empno未使用聚合函数，无法将同一员工的上下班时间合并到同一行。
2. 子查询内的ORDER BY无效，UNION ALL的子查询排序不会作用于最终结果集。
3. 虽然用LIKE匹配AM/PM能满足当前数据，但如果datetime是日期类型，这种方式不够严谨。

方式一：条件聚合（推荐） #

通过条件聚合直接在单查询中提取同一员工的上下班时间，逻辑简洁高效：

SELECT 
    empno,
    MAX(CASE WHEN datetime LIKE '%AM%' THEN datetime END) AS time_in,
    MAX(CASE WHEN datetime LIKE '%PM%' THEN datetime END) AS time_out
FROM august2023
WHERE empno LIKE '%5787%'
GROUP BY empno, DATE_FORMAT(STR_TO_DATE(datetime, '%m/%d/%Y %h:%i:%s %p'), '%Y-%m-%d')

• CASE语句筛选出对应时段的时间，MAX确保每天每个员工仅返回一条AM和PM记录（假设每天只有一次上下班打卡）。
• 按日期分组是为了支持员工多天打卡的场景，若只需聚合所有记录可省略日期分组。

方式二：日期类型适配（若datetime为标准日期字段） #

如果datetime是数据库原生日期时间类型，改用日期函数判断时段更可靠：

SELECT 
    empno,
    MAX(CASE WHEN HOUR(datetime) < 12 THEN datetime END) AS time_in,
    MAX(CASE WHEN HOUR(datetime) >= 12 THEN datetime END) AS time_out
FROM august2023
WHERE empno LIKE '%5787%'
GROUP BY empno, DATE(datetime)

方式三：自连接（原思路修正） #

若坚持用UNION ALL的思路，需通过自连接关联同一员工同一天的打卡记录：

SELECT 
    am.empno,
    am.time_in,
    pm.time_out
FROM (
    SELECT 
        empno, 
        datetime AS time_in,
        DATE(STR_TO_DATE(datetime, '%m/%d/%Y %h:%i:%s %p')) AS record_date
    FROM august2023
    WHERE empno LIKE '%5787%' AND datetime LIKE '%AM%'
) am
INNER JOIN (
    SELECT 
        empno, 
        datetime AS time_out,
        DATE(STR_TO_DATE(datetime, '%m/%d/%Y %h:%i:%s %p')) AS record_date
    FROM august2023
    WHERE empno LIKE '%5787%' AND datetime LIKE '%PM%'
) pm 
    ON am.empno = pm.empno 
    AND am.record_date = pm.record_date

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