表连接后去重并排除指定ID：保留重复ID的最后一条记录

针对你的问题，我分两步来解决：首先处理连接后重复ID只保留最后一条（默认按日期最新为准），然后移除table3中指定的ID。

核心思路说明 #

• 保留重复ID的最后一条：用窗口函数ROW_NUMBER()，按id分组，以date降序排序（确保最新的记录排在最前），只取行号为1的记录。
• 移除table3的ID：通过NOT EXISTS或者LEFT JOIN后过滤的方式，排除掉table3中存在的ID。

修改后的Python代码实现 #

方案一：使用NOT EXISTS排除table3的ID #

import datetime

start_date = datetime.datetime(2018,1,1)
# 构造带窗口函数的连接子查询
join_subquery = """
(
    SELECT 
        m.id, 
        m.filename, 
        n.type, 
        n.date,
        ROW_NUMBER() OVER (PARTITION BY m.id ORDER BY n.date DESC) AS rn
    FROM 
        (select id, filename from table1 where `group` = 1) m
    JOIN 
        (select id, type, date from table2 where is_good = 1 and date > '{start_date}') n 
    ON m.id = n.id
) AS joined_data
"""
# 最终SQL：筛选行号为1的记录，同时排除table3中的ID
cmd = f"""
SELECT id, filename, type, date
FROM {join_subquery.format(start_date=start_date.strftime('%Y-%m-%d'))}
WHERE rn = 1
AND NOT EXISTS (SELECT 1 FROM table3 t3 WHERE t3.id = joined_data.id)
"""

方案二：使用LEFT JOIN排除table3的ID #

如果你的数据库对LEFT JOIN的性能优化更好，可以用这种方式：

import datetime

start_date = datetime.datetime(2018,1,1)
join_subquery = """
(
    SELECT 
        m.id, 
        m.filename, 
        n.type, 
        n.date,
        ROW_NUMBER() OVER (PARTITION BY m.id ORDER BY n.date DESC) AS rn
    FROM 
        (select id, filename from table1 where `group` = 1) m
    JOIN 
        (select id, type, date from table2 where is_good = 1 and date > '{start_date}') n 
    ON m.id = n.id
) AS joined_data
"""
cmd = f"""
SELECT jd.id, jd.filename, jd.type, jd.date
FROM {join_subquery.format(start_date=start_date.strftime('%Y-%m-%d'))} jd
LEFT JOIN table3 t3 ON jd.id = t3.id
WHERE jd.rn = 1
AND t3.id IS NULL
"""

关键细节解释 #

• 窗口函数ROW_NUMBER()：PARTITION BY m.id按ID分组，ORDER BY n.date DESC让组内最新的记录行号为1，筛选rn=1就得到每个ID的最后一条记录。
• 保留字处理：group是SQL保留字，查询table1时要用反引号（`）包裹，避免语法错误。
• 排除指定ID：两种方式本质都是判断当前记录的ID不在table3中，可根据数据库性能和个人习惯选择。

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