如何用两个无唯一值的表向Pin Code Master表填充单州唯一数据？

1. 先处理City temp的同州多城市问题 #

由于同一个州（state）对应多个区和城市，我们需要按州分组，每个州只保留一条城市记录。这里用ROW_NUMBER()函数给每个州内的记录编号，取编号为1的那条（可通过排序规则指定选哪条）：

WITH unique_state_city AS (
    SELECT 
        state,
        district,
        city_name,
        state_name,
        ROW_NUMBER() OVER (PARTITION BY state ORDER BY entry_id DESC) AS rn
    FROM City_temp
)
SELECT state, district, city_name, state_name
FROM unique_state_city
WHERE rn = 1;

2. 关联临时表并填充Pin Code Master #

同时对Pin code temp按州去重，再和处理后的城市表关联，最后插入到目标表：

WITH unique_state_pin AS (
    SELECT 
        entry_id,
        pin_code,
        state,
        district,
        ROW_NUMBER() OVER (PARTITION BY state ORDER BY entry_id DESC) AS rn
    FROM Pin_code_temp
),
unique_state_city AS (
    SELECT 
        state,
        district,
        city_name,
        state_name,
        ROW_NUMBER() OVER (PARTITION BY state ORDER BY entry_id DESC) AS rn
    FROM City_temp
)
INSERT INTO Pin_Code_Master (entry_id, pin_code, state, district)
SELECT 
    usp.entry_id,
    usp.pin_code,
    usp.state,
    usp.district
FROM unique_state_pin usp
JOIN unique_state_city usc 
    ON usp.state = usc.state 
    AND usp.district = usc.district
WHERE usp.rn = 1 AND usc.rn = 1;

调整说明 #

• 若要改变每个州内选记录的规则，修改ORDER BY后的字段即可，比如按city_name ASC取首字母最小的城市，或按district排序。
• 如果Pin Code Master需要同步城市表的state_name、city_name字段，可扩展INSERT的字段列表，把对应字段加入SELECT语句。

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