SQL查询排除指定字段分组去重并保留最新状态记录的实现方法

核心需求是按除name、statustimestamp外的字段去重，保留每个分组中statustimestamp最新的一条记录对应的name值，不需要对name分组。

推荐方案（支持窗口函数的数据库，如MySQL 8.0+/PostgreSQL/SQL Server/Oracle） #

使用ROW_NUMBER()窗口函数给同分组内的记录按时间倒序打序号，筛选序号为1的记录即可：

WITH ranked_records AS (
    SELECT 
        trackingbatches.batchnumber, 
        requireddate, 
        jobno, 
        frames.frame_id, 
        frame_no, 
        groupdesc, 
        finishdesc, 
        finish2desc, 
        BOUGHTINFRAME, 
        NAME, 
        statustimestamp,
        -- 按去重字段分组，按时间倒序排序打序号
        ROW_NUMBER() OVER (
            PARTITION BY trackingbatches.batchnumber, requireddate, jobno, frames.frame_id, frame_no, groupdesc, finishdesc, finish2desc, BOUGHTINFRAME
            ORDER BY statustimestamp DESC
        ) AS rn
    FROM JOBQUOTEHEADER
    RIGHT JOIN trackingbatches ON JOBQUOTEHEADER.header_id=trackingbatches.header_id
    RIGHT JOIN frames ON trackingbatches.header_Id=frames.header_id
    RIGHT JOIN trackingstagesettings ON trackingbatches.status=trackingstagesettings.stage_id
    WHERE requireddate BETWEEN current_date-1 AND current_date
)
SELECT 
    batchnumber, 
    requireddate, 
    jobno, 
    frame_id, 
    frame_no, 
    groupdesc, 
    finishdesc, 
    finish2desc, 
    BOUGHTINFRAME, 
    NAME, 
    statustimestamp
FROM ranked_records
WHERE rn = 1 -- 只取每个分组最新的一条
ORDER BY JOBNO;

原理说明 #

• PARTITION BY后接的字段就是你需要去重的维度，也就是你原来GROUP BY里除了name的9个字段，保证这些字段完全相同的记录会被分到同一个窗口
• 同一个窗口内按statustimestamp DESC倒序排序，最新的记录序号为1
• 最后筛选rn=1即可得到每个分组唯一的最新记录

兼容老版本MySQL（5.x及以下，不支持窗口函数）的替代方案 #

先查询每个分组的最大时间戳，再关联回原表拿到对应的name值：

SELECT 
    t.batchnumber, 
    t.requireddate, 
    t.jobno, 
    t.frame_id, 
    t.frame_no, 
    t.groupdesc, 
    t.finishdesc, 
    t.finish2desc, 
    t.BOUGHTINFRAME, 
    t.NAME, 
    t.statustimestamp
FROM (
    -- 基础查询，和你原来的FROM、JOIN、WHERE逻辑一致
    SELECT 
        trackingbatches.batchnumber, 
        requireddate, 
        jobno, 
        frames.frame_id, 
        frame_no, 
        groupdesc, 
        finishdesc, 
        finish2desc, 
        BOUGHTINFRAME, 
        NAME, 
        statustimestamp
    FROM JOBQUOTEHEADER
    RIGHT JOIN trackingbatches ON JOBQUOTEHEADER.header_id=trackingbatches.header_id
    RIGHT JOIN frames ON trackingbatches.header_Id=frames.header_id
    RIGHT JOIN trackingstagesettings ON trackingbatches.status=trackingstagesettings.stage_id
    WHERE requireddate BETWEEN current_date-1 AND current_date
) t
INNER JOIN (
    -- 拿到每个去重分组的最大时间戳
    SELECT 
        trackingbatches.batchnumber, 
        requireddate, 
        jobno, 
        frames.frame_id, 
        frame_no, 
        groupdesc, 
        finishdesc, 
        finish2desc, 
        BOUGHTINFRAME,
        MAX(statustimestamp) AS max_ts
    FROM JOBQUOTEHEADER
    RIGHT JOIN trackingbatches ON JOBQUOTEHEADER.header_id=trackingbatches.header_id
    RIGHT JOIN frames ON trackingbatches.header_Id=frames.header_id
    RIGHT JOIN trackingstagesettings ON trackingbatches.status=trackingstagesettings.stage_id
    WHERE requireddate BETWEEN current_date-1 AND current_date
    GROUP BY trackingbatches.batchnumber, requireddate, jobno, frames.frame_id, frame_no, groupdesc, finishdesc, finish2desc, BOUGHTINFRAME
) t_max
ON 
    t.batchnumber = t_max.batchnumber
    AND t.requireddate = t_max.requireddate
    AND t.jobno = t_max.jobno
    AND t.frame_id = t_max.frame_id
    AND t.frame_no = t_max.frame_no
    AND t.groupdesc = t_max.groupdesc
    AND t.finishdesc = t_max.finishdesc
    AND t.finish2desc = t_max.finish2desc
    AND t.BOUGHTINFRAME = t_max.BOUGHTINFRAME
    AND t.statustimestamp = t_max.max_ts
ORDER BY t.JOBNO;

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