MySQL查询：如何将同日期的F1与F2记录正确关联分组？

需要从may2023表中提取员工编号（empno）包含5787的记录，将同日期的F1（上班）和F2（下班）分别对应为上班时间、下班时间。但当前SQL返回的下班时间始终是第一条F2记录，无法匹配对应日期。

错误的SQL语句 #

SELECT 
    d1.datetime AS time_in, d1.func AS func1, d2.datetime AS time_out, d2.func AS func2 
FROM 
    (SELECT datetime, func FROM `may2023` WHERE empno LIKE '%5787%' AND func = 'F1') d1, 
    (SELECT datetime, func FROM `may2023` WHERE empno LIKE '%5787%' AND func = 'F2') d2 GROUP BY d1.datetime

输入数据 #

当前错误输出 #

期望正确输出 #

错误原因 #

你的SQL使用了无连接条件的笛卡尔积，两个子查询直接关联会生成所有F1和F2的组合记录，再加上GROUP BY d1.datetime，在MySQL未开启ONLY_FULL_GROUP_BY时，会随机选取d2中的一条记录，导致所有F1都匹配到同一条F2记录，完全没有按日期关联。

正确的MySQL查询语句 #

方法1：按日期关联JOIN #

通过提取日期部分作为连接条件，确保同日期的F1和F2匹配：

SELECT 
    d1.datetime AS time_in,
    d1.func AS func1,
    d2.datetime AS time_out,
    d2.func AS func2
FROM 
    (SELECT datetime, func FROM `may2023` WHERE empno LIKE '%5787%' AND func = 'F1') d1
INNER JOIN 
    (SELECT datetime, func FROM `may2023` WHERE empno LIKE '%5787%' AND func = 'F2') d2
ON 
    DATE(d1.datetime) = DATE(d2.datetime);

方法2：条件聚合（更高效） #

只需扫描一次表，按日期分组后用CASE语句提取当天的上下班时间：

SELECT
    MAX(CASE WHEN func = 'F1' THEN datetime END) AS time_in,
    'F1' AS func1,
    MAX(CASE WHEN func = 'F2' THEN datetime END) AS time_out,
    'F2' AS func2
FROM `may2023`
WHERE empno LIKE '%5787%' AND func IN ('F1', 'F2')
GROUP BY DATE(datetime);

如果存在同一天多次上下班的情况，可根据实际需求替换MAX为MIN或其他聚合函数，确保取到对应时段的记录。

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