SQL查询如何按attrb_code分组筛选effective_date最新记录

要实现每个attrb_code仅保留effective_date最新的1条记录，最简洁的实现方式是使用窗口函数ROW_NUMBER()做分组排序后筛选，不需要修改你原有的过滤逻辑。

窗口函数实现（支持MySQL 8.0+、PostgreSQL、SQL Server、Oracle等主流数据库新版本） #

实现逻辑：

• 保留你原SQL里的所有过滤条件
• 新增窗口函数逻辑：按attrb_code分组，组内按effective_date倒序生成行号
• 最外层筛选行号为1的记录，就是每个分组下最新的条目

对应SQL代码：

SELECT 
    item_id, 
    attrb_code, 
    sub_item_id, 
    attrb_type, 
    description, 
    effective_date, 
    creation_date, 
    last_update_datetime, 
    last_user_id
FROM (
    SELECT 
        sad.item_id, 
        sad.attrb_code, 
        sad.sub_item_id, 
        sad.attrb_type, 
        sad.description, 
        sad.effective_date, 
        sad.creation_date, 
        sad.last_update_datetime, 
        sad.last_user_id,
        ROW_NUMBER() OVER (PARTITION BY sad.attrb_code ORDER BY sad.effective_date DESC) AS rn
    FROM table1 AS sad 
    WHERE NOT EXISTS 
    ( 
        SELECT 1 FROM table2 AS saa 
        WHERE sad.attrb_code = saa.attrb_code 
          AND sad.item_id = saa.item_id 
          AND saa.attrb_flag = 'N' 
    ) 
    AND sad.attrb_code IN ('VOICE', 'SMS2D', 'MMS2D', 'TRANS')
    AND sad.item_id = '???'
) t
WHERE rn = 1;

注意：如果同一个attrb_code下存在多条记录effective_date完全相同的情况，可以在窗口函数的ORDER BY后追加排序字段（比如last_update_datetime DESC）做二次排序，保证返回结果稳定，不会随机返回同日期下的任意记录。

兼容老版本数据库的关联子查询实现 #

如果你使用的数据库版本不支持窗口函数（比如MySQL 5.x），可以用关联子查询匹配每个attrb_code对应的最大effective_date来实现，代码如下：

SELECT DISTINCT 
    sad.item_id, 
    sad.attrb_code, 
    sad.sub_item_id, 
    sad.attrb_type, 
    sad.description, 
    sad.effective_date, 
    sad.creation_date, 
    sad.last_update_datetime, 
    sad.last_user_id 
FROM table1 AS sad 
WHERE NOT EXISTS 
( 
    SELECT 1 FROM table2 AS saa 
    WHERE sad.attrb_code = saa.attrb_code 
      AND sad.item_id = saa.item_id 
      AND saa.attrb_flag = 'N' 
) 
AND sad.attrb_code IN ('VOICE', 'SMS2D', 'MMS2D', 'TRANS')
AND sad.item_id = '???'
AND sad.effective_date = (
    SELECT MAX(sad2.effective_date)
    FROM table1 AS sad2
    WHERE sad2.attrb_code = sad.attrb_code
      AND sad2.item_id = sad.item_id
      AND NOT EXISTS (
          SELECT 1 FROM table2 AS saa2 
          WHERE sad2.attrb_code = saa2.attrb_code 
            AND sad2.item_id = saa2.item_id 
            AND saa2.attrb_flag = 'N' 
      )
      AND sad2.attrb_code IN ('VOICE', 'SMS2D', 'MMS2D', 'TRANS')
);

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