多表关联SQL查询异常：无法正确展示用户每日登录记录

你的核心问题出在聚合函数SUM()和GROUP BY的错误搭配，以及对LEFT JOIN后NULL值分组的特性不了解：

• GROUP BY chips.assigned_to：按用户分组后，聚合函数SUM()会把该用户当日所有登录的时长合并计算，同时MySQL会随机取该分组内的一条登录记录的其他字段（比如row_id、date_in等），所以每个用户只会显示一条记录，看不到多条登录明细。
• GROUP BY row_id：row_id是chips_inout表的唯一标识，每条登录记录对应一个row_id，所以能显示所有当日登录的明细，但未登录用户在chips_inout中没有对应行，LEFT JOIN后row_id为NULL，而GROUP BY会把所有NULL值的行合并成一组，导致所有未登录用户只显示一条NULL记录。
• 移除GROUP BY：因为查询中存在SUM()聚合函数，MySQL在未开启ONLY_FULL_GROUP_BY模式时，会默认将所有行聚合为一个分组，最终只返回一行汇总结果。

要同时满足「显示所有用户（含未登录）」和「显示用户当日多条登录记录」的需求，需要分离明细展示和总时长计算，用窗口函数替代GROUP BY来计算用户当日总时长，避免合并明细记录：

SELECT 
    chips.id as user_id,
    chips_inout.id as row_id,
    chips.assigned_to,
    chips.chip_id,
    chips_inout.date_in,
    chips_inout.date_out,
    TIME_FORMAT(chips_inout.date_in, '%H:%i:%s') as time_in,
    TIME_FORMAT(chips_inout.date_out, '%H:%i:%s') as time_out,
    -- 处理未登出用户的时长计算，用当前时间替代NULL的登出时间
    TIME_FORMAT(
        TIMEDIFF(IFNULL(chips_inout.date_out, NOW()), chips_inout.date_in),
        '%H:%i:%s'
    ) AS totaltime,
    -- 用窗口函数计算每个用户当日的总时长，不合并明细
    TIME_FORMAT(
        SUM(TIMEDIFF(IFNULL(chips_inout.date_out, NOW()), chips_inout.date_in)) 
        OVER (PARTITION BY chips.id),
        '%H:%i:%s'
    ) AS totaltime_daysum
FROM chips
LEFT JOIN chips_inout
    ON chips.chip_id = chips_inout.chip_id
    AND chips.chip_text = chips_inout.chip_text
    AND DATE(chips_inout.date_in) = '2024-01-22'
ORDER BY chips.assigned_to, chips_inout.date_in;

关键说明： #

1. 窗口函数OVER (PARTITION BY chips.id)：按用户ID分组计算总时长，但不会合并原有的明细记录，每条登录记录都会带上该用户的当日总时长。
2. IFNULL(chips_inout.date_out, NOW())：处理用户未登出的情况，用当前时间作为临时登出时间来计算时长，避免TIMEDIFF返回NULL。
3. 移除GROUP BY：保留所有LEFT JOIN后的行，未登录用户的chips_inout相关字段会显示为NULL，且每个未登录用户都会单独显示一行（不会合并）。

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