如何将同表JOIN查询转换为GROUP BY查询？附场景需求

针对你的需求——找出punches_history表中除TIMED字段差值小于2秒外，ClockNumber、PartNumber、Quantity、YYMMDD完全相同的记录，并纵向展示匹配的两条记录，以下是两种可行方案：

方案一：使用窗口函数（LEAD）实现纵向输出 #

利用窗口函数直接获取同组内的下一条记录，筛选符合条件的记录对后，通过UNION ALL将两条记录纵向拼接，避免横向拼接的繁琐查看。

WITH ranked_records AS (
    SELECT 
        *,
        -- 获取同组内下一条记录的TIMED值
        LEAD(TIMED) OVER (
            PARTITION BY ClockNumber, PartNumber, Quantity, YYMMDD 
            ORDER BY id
        ) AS next_timed,
        -- 获取同组内下一条记录的ID
        LEAD(id) OVER (
            PARTITION BY ClockNumber, PartNumber, Quantity, YYMMDD 
            ORDER BY id
        ) AS next_id,
        -- 获取同组内下一条完整记录
        LEAD(*) OVER (
            PARTITION BY ClockNumber, PartNumber, Quantity, YYMMDD 
            ORDER BY id
        ) AS next_record
    FROM punches_history
    WHERE ClockNumber != '10' AND CardColor = 'BLU'
)
-- 先输出当前符合条件的记录
SELECT * EXCEPT(next_timed, next_id, next_record)
FROM ranked_records
WHERE ABS(TIMED - next_timed) < 2 AND next_id = id + 1
UNION ALL
-- 再输出对应的匹配记录
SELECT next_record.*
FROM ranked_records
WHERE ABS(TIMED - next_timed) < 2 AND next_id = id + 1
ORDER BY YYMMDD DESC, ClockNumber DESC, id;

说明 #

• PARTITION BY按指定的相同字段分组，确保只在同组内查找匹配记录
• ORDER BY id保证按ID顺序获取下一条记录，避免重复匹配（如20和21、21和20）
• UNION ALL将两条匹配记录纵向排列，方便查看

方案二：GROUP BY分组筛选后关联原表 #

先通过GROUP BY找出存在符合条件记录对的分组，再关联回原表取出具体记录，实现纵向展示。

WITH valid_groups AS (
    SELECT 
        ClockNumber, PartNumber, Quantity, YYMMDD,
        MIN(id) AS min_id,
        MAX(id) AS max_id,
        MAX(TIMED) AS max_timed,
        MIN(TIMED) AS min_timed
    FROM punches_history
    WHERE ClockNumber != '10' AND CardColor = 'BLU'
    GROUP BY ClockNumber, PartNumber, Quantity, YYMMDD
    -- 筛选出只有两条连续ID且时间差小于2秒的组
    HAVING MAX(id) = MIN(id) + 1 
       AND ABS(max_timed - min_timed) < 2
)
SELECT ph.*
FROM punches_history ph
JOIN valid_groups vg 
    ON ph.ClockNumber = vg.ClockNumber
    AND ph.PartNumber = vg.PartNumber
    AND ph.Quantity = vg.Quantity
    AND ph.YYMMDD = vg.YYMMDD
    AND ph.id IN (vg.min_id, vg.max_id)
ORDER BY ph.YYMMDD DESC, ph.ClockNumber DESC, ph.id;

说明 #

• GROUP BY分组后，通过HAVING判断组内仅存在两条连续ID的记录，且时间差符合要求
• 关联原表后直接取出这两条记录，自然纵向排列，无需横向拼接

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