Informix数据库中匹配state_num前7位排查重复数据问题

问题根源 #

你的查询存在两个核心问题：

1. 子查询分组逻辑错误：子查询中GROUP BY cont_ref, state_num是按完整的state_num分组，而非前7位字符。这导致只有完全相同的state_num才会被判定为重复，和你“按前7位找重复”的需求不符，最终导致EXISTS条件大范围成立，返回大量无关数据。
2. char(15)类型的空格干扰：char(15)字段会自动补空格至15位，若state_num实际长度不足7位，空格会干扰匹配逻辑。

修正后的查询方案 #

方案一：先筛选重复前缀再关联原表 #

该方案先找出cont_ref和state_num前7位组合重复的记录，再关联原表获取完整数据，逻辑清晰且性能较好：

SELECT sf1.id_ref, sf1.cont_ref, sf1.formatted, sf1.state_num, sf1.type, sf1.state
FROM state_form sf1
JOIN (
    SELECT 
        cont_ref, 
        LEFT(RTRIM(state_num), 7) AS state_prefix
    FROM state_form
    WHERE state = 'MT'
    GROUP BY cont_ref, LEFT(RTRIM(state_num), 7)
    HAVING COUNT(DISTINCT state_num) > 1 -- 确保同一前缀下存在不同的state_num（带R/不带R）
) sf2 
    ON sf1.cont_ref = sf2.cont_ref 
    AND LEFT(RTRIM(sf1.state_num), 7) = sf2.state_prefix
WHERE sf1.state = 'MT';

方案二：修正EXISTS子查询逻辑 #

直接在子查询中判断是否存在同cont_ref、同前缀但不同state_num的记录：

SELECT id_ref, cont_ref, formatted, state_num, type, state
FROM state_form sf1
WHERE state = 'MT'
AND EXISTS (
    SELECT 1
    FROM state_form sf2
    WHERE sf2.cont_ref = sf1.cont_ref
      AND LEFT(RTRIM(sf2.state_num), 7) = LEFT(RTRIM(sf1.state_num), 7)
      AND sf2.state_num != sf1.state_num -- 排除自身，确保存在其他不同的state_num
);

关键说明 #

• 使用RTRIM(state_num)先去除char(15)字段末尾的空格，再取前7位，避免空格干扰匹配逻辑。
• 两个方案都通过COUNT(DISTINCT state_num) > 1或sf2.state_num != sf1.state_num确保返回的是前缀相同但state_num不同的记录（即同时包含带R和不带R的行），符合你的需求。

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