SQL查询ADDRESS_TABLE重复活跃记录返回多余值问题

• 处理规则：如果USER_TABLE中的用户在ADDRESS_TABLE中存在2条ACTIVE=1的重复地址数据，需要将其中1条记录的ACTIVE值修改为0
• 已知待处理重复数据：
  • ADDRESS_TABLE中编号14与15、18与19为两组重复地址，每组需将1条的活跃状态置为0
  • DOCUMENTS表中与上述地址关联的文档为编号2033与1400、3000与3001两组记录
• 表关联规则：ADDRESS_TABLE与DOCUMENTS通过公共字段INFID关联，INFID对应存储记录录入时间信息的关联表

原有查询SQL执行后返回多余结果，无法准确定位待更新记录，代码如下：

SELECT DISTINCT at.INFID, ut.UID FROM
  ADDRESS_TABLE at
  INNER JOIN users_table ut on ut.UID = at.UID
  INNER JOIN DOCUMENTS doc on ut.UID = doc.UID
  WHERE
ut.rowid <>
(
  SELECT
    MAX(ad.rowid)
  FROM
    ADDRESS_TABLE ad
    WHERE

    at.HOME = ad.HOME AND
    at.APP = ad.APP AND
    at.INDEX = ad.INDEX AND  
    at.ACTIVE = 1 and ad.ACTIVE = 1 HAVING count(*) > 1


)   ORDER BY ut.UID;

该SQL存在4个核心问题：

• 关联逻辑错误：关联DOCUMENTS表时错误使用UID作为关联字段，按照业务规则两表必须通过INFID关联，用UID关联会产生大量跨地址的无效匹配，直接导致结果冗余
• 判断逻辑漏洞：子查询中使用HAVING count(*) > 1的写法，会让非重复地址组的子查询返回空值，SQL中<> 空值的判断结果为未知，会误捞出大量不属于重复组的地址记录
• 分组维度缺失：地址重复判断没有绑定UID维度，可能跨用户匹配到地址内容完全相同的记录，造成跨用户的误判
• 表名可能不匹配：需求中用户表名为USER_TABLE，原有SQL中写为users_table，如果实际环境没有对应同名映射，也会导致查询逻辑异常

使用窗口函数按「用户+地址唯一字段」分组，直接定位每组重复地址中需要置为无效的记录，再通过正确的INFID字段关联文档表，即可得到准确的待处理记录集合。

查询待更新记录 #

WITH duplicate_addr AS (
  SELECT 
    INFID,
    UID,
    rowid,
    -- 按用户+地址核心字段分组，按rowid升序排列，每组第二条及以后的记录就是需要置为0的重复数据
    ROW_NUMBER() OVER (PARTITION BY UID, HOME, APP, `INDEX` ORDER BY rowid) AS rn
  FROM ADDRESS_TABLE
  WHERE ACTIVE = 1
)
SELECT 
  da.INFID,
  da.UID,
  da.rowid AS address_record_id,
  doc.DOC_ID AS related_document_id
FROM duplicate_addr da
INNER JOIN DOCUMENTS doc ON da.INFID = doc.INFID -- 必须用INFID关联文档表
WHERE da.rn > 1
ORDER BY da.UID;

执行上述查询后，返回结果应与标注的15、19两条地址记录、对应关联文档完全匹配，确认结果无误后即可执行更新操作。

更新重复地址状态 #

UPDATE ADDRESS_TABLE
SET ACTIVE = 0
WHERE rowid IN (
  SELECT rowid FROM (
    SELECT 
      rowid,
      ROW_NUMBER() OVER (PARTITION BY UID, HOME, APP, `INDEX` ORDER BY rowid) AS rn
    FROM ADDRESS_TABLE
    WHERE ACTIVE = 1
  ) t
  WHERE rn > 1
);

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