通过Left Join关联表修复错乱地址库的SQL查询需求

看起来你遇到的核心问题是：损坏表的街道地址是有效的，但关联的姓名、城市等信息错乱了，需要通过街道地址从原始表（85k条记录）里找回正确的信息，而且原始表有两个地址字段（postal_address1和physical_address1）都需要匹配对吧？之前的Left Join没得到预期结果，大概率是关联条件没覆盖两个地址字段，或者地址字符串的格式差异导致匹配失败。

我来给你几个可行的修正方案，你可以根据实际情况调整：

方案1：双Left Join + 合并匹配结果 #

这个方案会同时匹配原始表的邮政地址和物理地址，然后用COALESCE优先取邮政地址的信息（如果有的话），没有的话再取物理地址的。同时做了地址字符串的标准化（去空格、转大写），避免格式差异导致的匹配失败：

SELECT
  -- 损坏表的原始街道地址
  swl.scrubbed_street_address,
  -- 合并两个关联表的姓名，优先取邮政地址对应的姓名
  COALESCE(orc.customer_name, orp.customer_name) AS original_customer_name,
  COALESCE(orc.postal_city, orp.physical_city) AS original_city,
  COALESCE(orc.postal_state, orp.physical_state) AS original_state,
  COALESCE(orc.postal_zip, orp.physical_zip) AS original_zip
FROM aspen_scrubbed_but_wrong_list swl
-- 关联原始表的邮政地址
LEFT JOIN ASBCO_LIST_TO_CLEAN orc
  ON TRIM(UPPER(swl.scrubbed_street_address)) = TRIM(UPPER(orc.postal_address1))
-- 关联原始表的物理地址
LEFT JOIN ASBCO_LIST_TO_CLEAN orp
  ON TRIM(UPPER(swl.scrubbed_street_address)) = TRIM(UPPER(orp.physical_address1))
-- 可选：过滤掉完全匹配不到的记录
WHERE orc.customer_name IS NOT NULL OR orp.customer_name IS NOT NULL;

方案2：UNION ALL 拆分匹配（避免重复） #

如果同一个街道地址在原始表的邮政和物理地址里都存在，方案1会产生重复行，这个方案可以拆分匹配来源，同时避免重复：

-- 先匹配邮政地址的记录
SELECT
  swl.scrubbed_street_address,
  orc.customer_name,
  orc.postal_city AS city,
  orc.postal_state AS state,
  orc.postal_zip AS zip,
  'Postal Address' AS match_source -- 标记匹配的地址类型
FROM aspen_scrubbed_but_wrong_list swl
INNER JOIN ASBCO_LIST_TO_CLEAN orc
  ON TRIM(UPPER(swl.scrubbed_street_address)) = TRIM(UPPER(orc.postal_address1))

UNION ALL

-- 再匹配物理地址的记录，但排除已经在邮政地址匹配到的，避免重复
SELECT
  swl.scrubbed_street_address,
  orp.customer_name,
  orp.physical_city AS city,
  orp.physical_state AS state,
  orp.physical_zip AS zip,
  'Physical Address' AS match_source
FROM aspen_scrubbed_but_wrong_list swl
INNER JOIN ASBCO_LIST_TO_CLEAN orp
  ON TRIM(UPPER(swl.scrubbed_street_address)) = TRIM(UPPER(orp.physical_address1))
WHERE NOT EXISTS (
  SELECT 1
  FROM ASBCO_LIST_TO_CLEAN orc
  WHERE TRIM(UPPER(swl.scrubbed_street_address)) = TRIM(UPPER(orc.postal_address1))
);

为什么你的原始Left Join没生效？ #

大概率是这两个原因：

1. 关联条件只覆盖了一个地址字段：你可能只关联了postal_address1或者physical_address1，漏掉了另一个，导致很多损坏表的地址找不到匹配项
2. 地址字符串未标准化：原始表和损坏表的地址可能有大小写差异（比如"Main St" vs "main st"）、多余空格（比如" 123 Main St " vs "123 Main St"）、缩写不一致（比如"St." vs "Street"），这些都会导致精确匹配失败

额外优化建议 #

• 更深度的地址标准化：如果缩写差异大，可以用REPLACE替换常见缩写，比如：

  -- 示例：替换街道缩写
  REPLACE(REPLACE(TRIM(UPPER(swl.scrubbed_street_address)), ' ST ', ' STREET '), ' AVE ', ' AVENUE ')
  

• 检查匹配率：先跑个统计看看有多少损坏表的地址能匹配到原始表，方便评估效果：

  SELECT
    COUNT(*) AS total_scrubbed_records,
    SUM(CASE WHEN orc.postal_address1 IS NOT NULL THEN 1 ELSE 0 END) AS postal_matches,
    SUM(CASE WHEN orp.physical_address1 IS NOT NULL THEN 1 ELSE 0 END) AS physical_matches,
    SUM(CASE WHEN orc.postal_address1 IS NOT NULL OR orp.physical_address1 IS NOT NULL THEN 1 ELSE 0 END) AS total_matches
  FROM aspen_scrubbed_but_wrong_list swl
  LEFT JOIN ASBCO_LIST_TO_CLEAN orc
    ON TRIM(UPPER(swl.scrubbed_street_address)) = TRIM(UPPER(orc.postal_address1))
  LEFT JOIN ASBCO_LIST_TO_CLEAN orp
    ON TRIM(UPPER(swl.scrubbed_street_address)) = TRIM(UPPER(orp.physical_address1));
  

• 处理重复匹配：如果原始表有多个相同街道地址的记录，你可能需要加额外的过滤条件（比如匹配邮编的前几位），或者用ROW_NUMBER()来取最新/最相关的记录。

注意：上面的SQL里的字段名（比如scrubbed_street_address、customer_name）是我假设的，你需要替换成实际表中的字段名哦！

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