内连接多行取首行：查找新数据表缺失配对SQL问题求助

我需要编写SQL查询，在关联查询中从多行选取首行，找出new_data_table中缺失的配对数据。现有两张表history_table和new_data_table，按配对规则，新数据需成对出现（如F1对应N1、FW对应SP等）。

表结构及数据 #

history_table #

Id  Tnum    Snum    Rnum
------------------------
1   A1234   F1       0
2   A1234   N1       0
3   B1234   SP       2
4   B1234   FW       2
5   A1234   F1       1
6   A1234   N1       1
7   C1234   I1       0
8   C1234   I2       0
9   A1234   F1       2
10  A1234   N1       2

new_data_table #

Id  Tnum    Snum    Rnum
-------------------------
1   A1234   F1       3
2   B1234   FW       3
3   C1234   I2       1

预期输出 #

History_Id Tnum    Snum    Rnum
 --------------------------------
  10        A1234    N1      2
  3         B1234    SP      2
  7         C1234    I1      0

问题查询 #

我编写的查询仅返回1条记录，无法得到预期的3条结果：

WITH previous_latest_missing_pair as 
(
    SELECT *
    FROM history_table
    WHERE id IN (SELECT MAX(h.id)
                 FROM history_table h 
                 JOIN new_data_table n ON n.Tnum = h.Tnum 
                 WHERE h.Snum = CASE
                                    WHEN n.Snum = 'SP' THEN 'FW'
                                    WHEN n.Snum = 'FW' THEN 'SP'
                                    WHEN n.Snum = 'F1' THEN 'N1'
                                    WHEN n.Snum = 'N1' THEN 'F1'
                                    WHEN n.Snum = 'I1' THEN 'I2'
                                    WHEN n.Snum = 'I2' THEN 'I1'
                                END)
)
SELECT h.*
FROM history_table h
JOIN previous_latest_missing_pair p ON p.Tnum = h.Tnum
                                    AND p.Snum = h.Snum
                                    AND p.Rnum = h.Rnum

修正后的查询 #

原查询的问题在于子查询中MAX(h.id)会返回所有符合条件记录的全局最大值，而非每个Tnum+配对Snum分组的最大值，因此只能得到单条结果。改用窗口函数按分组取最新记录即可解决：

WITH pair_mapping AS (
    -- 为每条新数据记录生成对应的缺失配对Snum
    SELECT 
        n.Tnum,
        CASE
            WHEN n.Snum = 'SP' THEN 'FW'
            WHEN n.Snum = 'FW' THEN 'SP'
            WHEN n.Snum = 'F1' THEN 'N1'
            WHEN n.Snum = 'N1' THEN 'F1'
            WHEN n.Snum = 'I1' THEN 'I2'
            WHEN n.Snum = 'I2' THEN 'I1'
        END AS required_snum
    FROM new_data_table n
),
latest_history AS (
    -- 为每个Tnum+Snum分组标记最新记录（按Rnum降序、Id降序）
    SELECT 
        h.Id AS History_Id,
        h.Tnum,
        h.Snum,
        h.Rnum,
        ROW_NUMBER() OVER (PARTITION BY h.Tnum, h.Snum ORDER BY h.Rnum DESC, h.Id DESC) AS rn
    FROM history_table h
    JOIN pair_mapping pm ON h.Tnum = pm.Tnum AND h.Snum = pm.required_snum
)
-- 筛选每个分组的第一条记录（最新的缺失配对记录）
SELECT History_Id, Tnum, Snum, Rnum
FROM latest_history
WHERE rn = 1;

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