通过JOIN按关联表字段分组时排除最新日期的SQL实现

方案一：使用窗口函数（推荐） #

窗口函数可直接给每个id的日期排序，精准排除最新记录，逻辑清晰且性能较好：

SELECT t2.sid, t1.date
FROM (
    SELECT 
        id, 
        date,
        -- 按id分组，日期降序排序，最新日期标记为1
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS rn
    FROM dbo.table1
    WHERE num = 000 -- 若num为字符串类型，请改为'000'
) t1
-- 原LEFT JOIN加t2.sid IS NOT NULL等价于INNER JOIN，直接用INNER JOIN更高效
INNER JOIN dbo.table2 t2 ON t1.id = t2.id
WHERE t1.rn > 1 -- 排除最新日期（rn=1的是该id下的最新记录）
  AND t2.sid NOT LIKE '%#########%'
GROUP BY t2.sid, t1.date

逻辑说明 #

1. 子查询中通过ROW_NUMBER()给每个id下的日期按降序编号，最新日期的编号为1；
2. 外层筛选rn > 1的记录，即排除每个id的最新日期；
3. 关联table2后过滤sid的条件，最后按sid和date分组。

方案二：通过JOIN排除最大日期 #

如果不支持窗口函数（如旧版本SQL Server），可以先预查询每个id的最大日期，再通过左连接排除：

SELECT t2.sid, t1.date
FROM dbo.table1 t1
INNER JOIN dbo.table2 t2 ON t1.id = t2.id
-- 左连接每个id的最大日期记录
LEFT JOIN (
    SELECT id, MAX(date) AS max_date
    FROM dbo.table1
    WHERE num = 000
    GROUP BY id
) t1_max ON t1.id = t1_max.id AND t1.date = t1_max.max_date
WHERE t1.num = 000
  AND t2.sid NOT LIKE '%#########%'
  AND t1_max.max_date IS NULL -- 排除匹配到最大日期的记录
GROUP BY t2.sid, t1.date

逻辑说明 #

1. 子查询先找出每个id对应的最新日期；
2. 左连接原table1，当t1.date等于该id的最大日期时，t1_max.max_date不为空；
3. 筛选t1_max.max_date IS NULL的记录，即排除了最新日期的行。

原查询的问题分析 #

你原查询中用NOT IN的逻辑本身没问题，但可能存在以下隐患：

• 若某个id的MAX(date)为NULL，NOT IN会导致整个条件不成立（因为NULL参与的比较结果为未知）；
• 原LEFT JOIN后加t2.sid IS NOT NULL等价于INNER JOIN，写法冗余；
• 当table1中存在多个相同id+date的记录时，NOT IN会全部排除，这可能符合预期，但窗口函数的方式更直观可控。

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