基于匹配值获取关联唯一值的SQL实现方案咨询

需求说明 #

基于身份表#input中的共享值检索编号（No）数据，需遵循以下规则获取关联编号的唯一值：

• 规则1：若No列存在重复值，且其中某行的Id（来自#input）与No列相等，则仅保留该行；
• 规则2：若No列存在重复值，但无任何行的Id与No列相等，则可保留任意一行，确保最终结果中No列无重复。

示例代码（原实现） #

-- 现有数据表
SELECT *
INTO #data
FROM (
    VALUES (1, 05202502)
          ,(3, 05202503)
          ,(5, 05202502)
          ,(6, 05202501)
          ,(8, 05202501)
          ,(32, 05202501)
      ) d([No], [Value])

-- 待匹配的身份表
SELECT *
INTO #input
FROM (
    VALUES (1), (3), (5), (6), (8)
     ) i([Id])

-- 原查询逻辑：基于Input的Id匹配Value，再关联获取No
SELECT i.[Id]
      ,d2.[No]
FROM #input i
LEFT JOIN #data d1
ON i.[Id] = d1.[No]
LEFT JOIN #data d2
ON d1.[Value] = d2.[Value]

DROP TABLE #input
DROP TABLE #data

当前问题与期望结果 #

原查询返回的结果中No列存在重复，期望得到的结果如下：

注：最后一行的Id可为6或8

解决方案 #

核心思路是先对#data按Value分组，筛选出每组符合规则的唯一No值，再与#input关联。具体实现如下：

-- 现有数据表
SELECT *
INTO #data
FROM (
    VALUES (1, 05202502)
          ,(3, 05202503)
          ,(5, 05202502)
          ,(6, 05202501)
          ,(8, 05202501)
          ,(32, 05202501)
      ) d([No], [Value])

-- 待匹配的身份表
SELECT *
INTO #input
FROM (
    VALUES (1), (3), (5), (6), (8)
     ) i([Id])

-- 按规则筛选唯一No并关联Input的Id
WITH ranked_data AS (
    SELECT 
        d.[No],
        d.[Value],
        -- 标记当前No是否在Input的Id中，优先保留这类行
        CASE WHEN EXISTS (SELECT 1 FROM #input i WHERE i.Id = d.No) THEN 1 ELSE 0 END AS is_matched_id,
        -- 按规则排序：优先选Id匹配的行，否则选任意一行
        ROW_NUMBER() OVER (PARTITION BY d.Value ORDER BY CASE WHEN EXISTS (SELECT 1 FROM #input i WHERE i.Id = d.No) THEN 0 ELSE 1 END, d.No) AS rn
    FROM #data d
),
unique_no_per_value AS (
    SELECT No, Value FROM ranked_data WHERE rn = 1
)
-- 关联Input获取对应的Id
SELECT 
    i.Id,
    unv.No
FROM #input i
LEFT JOIN #data d1 ON i.Id = d1.No
LEFT JOIN unique_no_per_value unv ON d1.Value = unv.Value
-- 去重确保No唯一
GROUP BY i.Id, unv.No
ORDER BY i.Id, unv.No;

DROP TABLE #input
DROP TABLE #data

逻辑说明 #

1. ranked_data CTE：给#data每行标记是否存在对应的Input Id（is_matched_id），并按Value分组排序，优先保留Id=No的行；
2. unique_no_per_value CTE：筛选出每组Value中排名第一的No，确保每个Value对应唯一符合规则的No；
3. 最终查询：关联#input和筛选后的唯一No，通过GROUP BY确保结果中No无重复，同时保留对应的Input Id。

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