JOIN查询合并两行SQL数据及punch_in/punch_out列调整技术咨询

我之前处理过类似的打卡时间合并需求，看你的SQL结构，问题大概率出在两次关联clean_home_status_log时没有过滤状态，导致数据出现错误配对或者笛卡尔积，让punch_in和punch_out的值乱掉了。下面给你两种调整方案，你可以根据业务场景选：

方案一：用条件聚合（推荐，更高效） #

这种方式不需要多次关联同一张表，直接通过CASE语句按状态筛选时间，避免冗余关联：

SELECT 
    c.first_name as customer_name, 
    ch.id as clean_home_id,
    -- 取对应状态的打卡时间，用MAX确保只取最新的一条（如果有多条的话）
    MAX(CASE WHEN sl.status = 'PUNCH_IN' THEN sl.modified_date END) as punch_in,
    MAX(CASE WHEN sl.status = 'PUNCH_OUT' THEN sl.modified_date END) as punch_out,
    e.first_name as employee_name, 
    ch.employee_id,
    ISNULL(chlog.timespent, 0) AS timespent 
FROM clean_home ch
INNER JOIN customer c on ch.customer_id = c.id
INNER JOIN employee e on ch.employee_id = e.id
-- 左关联状态日志表，避免丢失无打卡记录的订单
LEFT JOIN clean_home_status_log sl on sl.clean_home_id = ch.id
LEFT JOIN clean_home_log chlog on chlog.clean_home_id = ch.id -- 假设你的关联条件是这个，按需调整
GROUP BY 
    c.first_name, 
    ch.id,
    e.first_name, 
    ch.employee_id,
    ISNULL(chlog.timespent, 0)

方案二：保留两次关联，但添加状态过滤 #

如果你习惯用多次关联的写法，一定要给每个关联的日志表加上状态筛选条件，不然会出现所有状态记录的交叉匹配：

SELECT 
    c.first_name as customer_name, 
    ch.id as clean_home_id,
    sl.modified_date as punch_in,
    sl2.modified_date as punch_out,
    e.first_name as employee_name, 
    ch.employee_id,
    ISNULL(chlog.timespent, 0) AS timespent 
FROM clean_home ch
INNER JOIN customer c on ch.customer_id = c.id
INNER JOIN employee e on ch.employee_id = e.id
-- 只关联打卡进的记录
LEFT JOIN clean_home_status_log sl 
    on sl.clean_home_id = ch.id 
    AND sl.status = 'PUNCH_IN' -- 替换成你表中实际的"打卡进"状态值，比如'IN'/'START'
-- 只关联打卡出的记录
LEFT JOIN clean_home_status_log sl2 
    on sl2.clean_home_id = ch.id 
    AND sl2.status = 'PUNCH_OUT' -- 替换成实际的"打卡出"状态值，比如'OUT'/'END'
LEFT JOIN clean_home_log chlog on chlog.clean_home_id = ch.id

关键注意点： #

• 一定要确认clean_home_status_log表中用来区分打卡状态的字段（比如status）的具体枚举值，别写错了
• 如果一个订单有多次打卡进/出的记录，用MAX()/MIN()取你需要的那条（最新/最早）
• 用LEFT JOIN代替INNER JOIN关联日志表，避免那些还没完成打卡出的订单被过滤掉

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