多列精确/模糊匹配场景下SQL重复记录查询问题咨询

你的核心判断完全正确：这类跨列识别重复记录的查询逻辑框架高度通用，仅需根据不同列的匹配规则（精确相等/相似度阈值）调整过滤条件即可，无需针对每个场景重构整体写法。

基础场景实现方案 #

你之前用GROUP BY + HAVING COUNT(*) >1只能返回分组字段、无法带出C、F这类非分组展示字段的问题，用窗口函数或者CTE分层处理就能解决，不需要把展示字段加入分组逻辑。

• 全精确匹配场景（A、B、D、E列完全一致，展示C、F列）
  直接用窗口函数按精确匹配列分区计数，筛出同组记录数大于1的所有条目即可，可直接带出任意需要展示的字段：

  WITH match_group AS (
      SELECT
          *,
          COUNT(*) OVER(PARTITION BY A, B, D, E) AS group_record_cnt
      FROM your_target_table
  )
  SELECT C, F, A, B, D, E
  FROM match_group
  WHERE group_record_cnt > 1;
  

• 混合匹配场景（A、B、D列精确一致，E列相似匹配，展示C、F列）
  不要直接把相似度计算逻辑塞进GROUP BY，分两步处理即可：第一步先按精确匹配列圈定同组记录，第二步在同组内做相似度校验，筛选存在同组相似记录的条目。

示例SQL问题排查 #

你写的查询会混入不符合规则的单条记录，核心是三个逻辑漏洞：

1. 隐式交叉连接（两表直接用逗号关联）未加防重复配对条件，没有类似tickets.id < ticketsB.id的约束，会出现同一条记录反复作为左右表配对、甚至边缘情况下自配对的问题
2. 硬编码的长度差规则length("TicketsB".num) - length("Tickets".num) = 1和编辑距离条件存在逻辑冲突，既会漏判长度差为0但编辑距离为1的相似对，也会误判长度差为1但编辑距离超阈值的无效对
3. 配对CTE中直接加了LIMIT 100000截断配对结果，会导致部分配对关系只返回单边记录，最终UNION时就会出现孤立的无效单条记录

修正后的参考写法 #

WITH base_with_key AS (
    -- 第一层：生成精确匹配组ID、单条记录唯一ID
    SELECT
        vid,
        id,
        num,
        date,
        amount,
        -- 精确匹配组ID，对应你需要的E_KEY
        to_hex(MD5(TO_UTF8(CONCAT(vid, CAST(date AS VARCHAR), CAST(amount AS VARCHAR))))) AS E_KEY,
        -- 单条记录唯一键，对应你需要的R_KEY
        to_hex(MD5(TO_UTF8(CONCAT(vid, id, num, CAST(date AS VARCHAR))))) AS R_KEY
    FROM TicketTable
),
similar_pair AS (
    -- 第二层：同精确组内做相似匹配，加id大小判断避免重复配对、自配对
    SELECT DISTINCT t1.R_KEY AS hit_rkey
    FROM base_with_key t1
    INNER JOIN base_with_key t2
        ON t1.E_KEY = t2.E_KEY
        AND t1.id < t2.id
        AND levenshtein_distance(t1.num, t2.num) < 2
)
-- 最终查询：取出所有命中重复规则的记录，可自由加需要展示的字段
SELECT
    b.E_KEY,
    b.R_KEY,
    b.vid,
    b.id,
    b.num,
    b.date,
    b.amount
FROM base_with_key b
INNER JOIN similar_pair s
    ON b.R_KEY = s.hit_rkey
ORDER BY b.vid, b.date, b.amount;

通用逻辑框架 #

不管是全精确匹配还是带相似规则的匹配，都可以套用固定三层结构，仅需调整对应匹配条件即可：

1. 基础层：给原始表生成两个标识——精确匹配组ID（由所有需要精确匹配的字段拼接生成）、单条记录唯一键
2. 配对层：根据规则识别重复关系：全精确匹配直接统计同组记录数即可；带相似匹配的场景，在同精确组内做相似度计算，取出所有满足匹配关系的记录唯一键
3. 结果层：关联取出所有命中重复规则的记录，自由选择需要展示的字段，不受分组逻辑限制

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