MySQL中使用派生字段查询重复地址多条记录的问题

问题分析 #

你的尝试查询存在几个关键问题：

• 用用户变量@property/@property2做分组依据逻辑错误，MySQL的GROUP BY应直接使用计算字段，变量赋值时机可能导致分组结果偏差。
• 直接CONCAT地址字段时，若某字段为NULL，整个拼接结果会变为NULL，同时多余空格会让实际相同的地址被判定为不同值，导致漏匹配。
• 未同时覆盖有UPRN和无UPRN两种场景的重复记录筛选。

修正后的查询代码 #

方案1：兼容MySQL 5.x及以上版本

SELECT rep.*
FROM rep_base_report rep
JOIN (
    -- 找出近3个月内LODGED状态下，重复的UPRN或重复的标准化地址
    SELECT 
        CASE 
            WHEN UPRN IS NOT NULL THEN UPRN
            ELSE TRIM(REPLACE(CONCAT(
                COALESCE(ADDRESS1, ''), ' ',
                COALESCE(ADDRESS2, ''), ' ',
                COALESCE(ADDRESS3, ''), ' ',
                COALESCE(TOWN, ''), ' ',
                COALESCE(POSTCODE, '')
            ), '  ', ' ')) 
        END AS group_key
    FROM rep_base_report
    WHERE STATUS = 'LODGED'
      AND GENERATED_DATE > CURDATE() - INTERVAL 3 MONTH
    GROUP BY group_key
    HAVING COUNT(*) > 1
) AS dup_groups 
ON (
    -- 关联条件：要么UPRN匹配，要么无UPRN时标准化地址匹配
    (rep.UPRN IS NOT NULL AND rep.UPRN = dup_groups.group_key)
    OR
    (rep.UPRN IS NULL AND TRIM(REPLACE(CONCAT(
        COALESCE(rep.ADDRESS1, ''), ' ',
        COALESCE(rep.ADDRESS2, ''), ' ',
        COALESCE(rep.ADDRESS3, ''), ' ',
        COALESCE(rep.TOWN, ''), ' ',
        COALESCE(rep.POSTCODE, '')
    ), '  ', ' ')) = dup_groups.group_key)
)
WHERE rep.STATUS = 'LODGED'
  AND rep.GENERATED_DATE > CURDATE() - INTERVAL 3 MONTH
ORDER BY 
    CASE WHEN rep.UPRN IS NOT NULL THEN rep.UPRN ELSE dup_groups.group_key END,
    rep.GENERATED_DATE DESC;

方案2：MySQL 8.0+版本（使用CTE简化逻辑）

WITH standardized_reports AS (
    SELECT 
        *,
        -- 生成标准化地址：替换空字段为空白，合并多余空格，去掉首尾空格
        TRIM(REPLACE(CONCAT(
            COALESCE(ADDRESS1, ''), ' ',
            COALESCE(ADDRESS2, ''), ' ',
            COALESCE(ADDRESS3, ''), ' ',
            COALESCE(TOWN, ''), ' ',
            COALESCE(POSTCODE, '')
        ), '  ', ' ')) AS standardized_address
    FROM rep_base_report
    WHERE STATUS = 'LODGED'
      AND GENERATED_DATE > CURDATE() - INTERVAL 3 MONTH
),
duplicate_groups AS (
    SELECT 
        CASE 
            WHEN UPRN IS NOT NULL THEN UPRN
            ELSE standardized_address
        END AS group_key
    FROM standardized_reports
    GROUP BY group_key
    HAVING COUNT(*) > 1
)
SELECT sr.*
FROM standardized_reports sr
JOIN duplicate_groups dg 
ON (
    (sr.UPRN IS NOT NULL AND sr.UPRN = dg.group_key)
    OR
    (sr.UPRN IS NULL AND sr.standardized_address = dg.group_key)
)
ORDER BY 
    CASE WHEN sr.UPRN IS NOT NULL THEN sr.UPRN ELSE dg.group_key END,
    sr.GENERATED_DATE DESC;

关键优化点 #

• 地址标准化：用COALESCE将NULL字段转为空字符串，REPLACE合并多余空格，TRIM去除首尾空格，避免因格式差异导致地址匹配失败。
• 双场景覆盖：同时处理有UPRN（按UPRN分组）和无UPRN（按标准化地址分组）的重复记录。
• 逻辑严谨性：通过JOIN关联重复分组，确保只返回符合条件的重复记录，避免子查询IN的潜在问题。

内容的提问来源于stack exchange，提问作者my-socrates-note