基于州、市、县配置表关联person表提取数据去重问题求助

你当前的问题是：用JOIN关联setup配置表和person人员表时，同一个人员会匹配多条符合条件的配置规则，导致查询结果出现重复记录，想不用DISTINCT来实现更高效的查询。

重复原因分析 #

比如NY州Bronx县的人员，既会匹配NY,*,*这条全量规则，又会匹配NY,*,Bronx这条县规则，JOIN后就会生成两条相同的人员记录；CA州的部分人员也可能同时匹配市规则和县规则，导致重复。

优化方案 #

方案1：用EXISTS子查询（最推荐）

EXISTS只检查是否存在符合条件的配置，不会因为多条匹配产生重复记录，而且数据库执行时找到匹配就停止检查，性能更优。

SELECT  
    e.emplid,
    e.first_name,
    e.last_name,
    e.state,
    e.county,
    e.city
FROM
    person e
WHERE
    sysdate BETWEEN e.eff_dt AND e.end_eff_dt 
    AND e.hr_Status <> 'T'
    AND e.STAFF = 'N'
    AND e.union = 'NU'
    AND e.country = 'USA'
    AND EXISTS (
        SELECT 1
        FROM setup S
        WHERE e.state = S.State
          AND (e.city = S.city OR S.city = '*') 
          AND (e.county = S.county OR S.county = '*')
          AND sysdate BETWEEN S.eff_dt AND S.end_eff_dt
    );

方案2：先简化配置表，去掉冗余规则

观察配置表能发现，NY,*,*已经覆盖了NY,*,Bronx、NY,*,Kings的所有人员；AZ,*,*也覆盖了AZ州的全部人员。先把这些被包含的冗余规则过滤掉，再关联就不会产生重复匹配。

WITH simplified_setup AS (
    -- 保留最宽泛的全量规则
    SELECT s.*
    FROM setup s
    WHERE s.city = '*'
      AND s.county = '*'
      AND sysdate BETWEEN s.eff_dt AND s.end_eff_dt
    UNION ALL
    -- 保留那些没有全量规则覆盖的细分规则（比如CA的市、县规则）
    SELECT s.*
    FROM setup s
    WHERE NOT EXISTS (
        SELECT 1
        FROM setup s2
        WHERE s2.State = s.State
          AND s2.city = '*'
          AND s2.county = '*'
          AND sysdate BETWEEN s2.eff_dt AND s2.end_eff_dt
    )
      AND sysdate BETWEEN s.eff_dt AND s.end_eff_dt
)
SELECT  
    e.emplid,
    e.first_name,
    e.last_name,
    e.state,
    e.county,
    e.city
FROM
    person e
JOIN simplified_setup S 
    ON e.state = S.State
    AND (e.city = S.city OR S.city = '*') 
    AND (e.county = S.county OR S.county = '*')
WHERE
    sysdate BETWEEN e.eff_dt AND e.end_eff_dt 
    AND e.hr_Status <> 'T'
    AND e.STAFF = 'N'
    AND e.union = 'NU'
    AND e.country = 'USA';

方案3：用窗口函数去重

如果必须保留JOIN的方式（比如后续要用到配置表的字段），可以用窗口函数给每个人员的匹配记录编号，只取第一条。

WITH joined_data AS (
    SELECT  
        e.emplid,
        e.first_name,
        e.last_name,
        e.state,
        e.county,
        e.city,
        -- 按人员分组，优先取全量规则的匹配记录（编号为1）
        ROW_NUMBER() OVER (PARTITION BY e.emplid ORDER BY CASE WHEN S.city = '*' AND S.county = '*' THEN 1 ELSE 2 END) AS rn
    FROM
        person e
    JOIN setup S 
        ON e.state = S.State
        AND (e.city = S.city OR S.city = '*') 
        AND (e.county = S.county OR S.county = '*')
        AND sysdate BETWEEN S.eff_dt AND S.end_eff_dt
    WHERE
        sysdate BETWEEN e.eff_dt AND e.end_eff_dt 
        AND e.hr_Status <> 'T'
        AND e.STAFF = 'N'
        AND e.union = 'NU'
        AND e.country = 'USA'
)
SELECT emplid, first_name, last_name, state, county, city
FROM joined_data
WHERE rn = 1;

方案对比 #

• 方案1最简洁高效，适合只需要提取人员数据的场景，完全避免重复。
• 方案2适合配置表规则较多、存在大量包含关系的场景，减少JOIN的匹配次数。
• 方案3适合需要保留配置表关联信息的场景，灵活控制去重逻辑。

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