如何使用SQL Server的CTE语法关联两表并去除重复数据？

表结构说明 #

表a #

province_id
1
1
1
1
1
2
2
2
2
2
3
3
3
3
3
...

• 仅含province_id列，共约150万行，存在大量重复数据，唯一值约200个

表b #

f_name       city_value        label
 city_id       1            Austin
 ac_id         1            Mayor: Jason Lee
 df_id         1            Republican
 ef_id         1            Property tax is high
 city_id       2            Dallas
 dfg_id        2            Mayor: ABC
 fth_id        2            Republican-3
 tpr_id        2            Property tax is low
city_id        3            Waco
 ddd_id        3            Mayor: DEF
 ers_id        3            Republican-4
 qws_id        3            Property tax is middle
city_id        4            Arlington
 zxg_id        4            Mayor: HGR
 zUg_id        4            Republican+4
...

• 共约40万行，包含f_name、city_value、label三列
• f_name与label关联：当f_name='city_id'时，label为对应城市名称
• 同一城市对应唯一city_value，同一city_value下label内容唯一，仅需保留城市名相关记录

需求 #

• 对表a的province_id列进行去重
• 表b仅保留f_name='city_id'的城市名记录
• 用去重后的province_id与city_value作为关联键关联两表，结果行数需等于表a去重后的province_id数量

问题说明 #

此前使用的SQL代码无法有效满足需求：

SELECT DISTINCT a.province_id, b.label
       FROM a
       JOIN b ON 
        a.province_id=b.city_value;

解决方案（SQL Server CTE实现） #

WITH DeduplicatedProvince AS (
    -- 对表a的province_id去重，得到唯一省份ID集合
    SELECT DISTINCT province_id
    FROM a
),
CityNameRecords AS (
    -- 筛选表b中仅保留城市名的记录
    SELECT city_value, label
    FROM b
    WHERE f_name = 'city_id'
)
-- 关联两个CTE，确保结果行数匹配去重后的省份ID数量
SELECT dp.province_id, cn.label
FROM DeduplicatedProvince dp
LEFT JOIN CityNameRecords cn ON dp.province_id = cn.city_value;

代码说明 #

1. DeduplicatedProvince CTE：先对表a做去重处理，直接得到唯一的province_id列表，避免后续关联时因原表重复数据导致结果行数膨胀
2. CityNameRecords CTE：提前过滤表b的无关数据，仅保留城市名记录，减少关联时的数据量，提升查询效率
3. 使用LEFT JOIN可确保所有去重后的省份ID都出现在结果中（若表b无对应城市记录，label会显示为NULL；若仅需保留有对应城市的记录，可改为INNER JOIN）

原代码问题在于：先关联再去重，未提前过滤表b的无关记录，可能导致关联后出现多余行，逻辑冗余且效率较低。

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