一对多关联表SQL查询结果过滤问题及修正需求

问题根源 #

你的SQL返回不符合条件的记录，核心问题在于：

• NOT EXISTS子句的逻辑只检查了当前行关联的NBR是否有违规DIV，但实际需求是只要某个NBR在Service表中存在任意一条DIV属于指定范围的记录，就排除该NBR的所有记录。
• 左连接的使用会引入无对应Service记录的Process数据，不符合基于Service记录筛选的需求。

修正后的SQL #

SELECT DISTINCT
    s.nbr,
    s.model
FROM
    service s
JOIN process p ON p.nbr = s.nbr
WHERE
    -- Process表过滤条件
    p.unit = 'MC' 
    AND p.car = 'M' 
    AND p.bank = '1'
    AND p.paid IN ('NY', 'NJ')
    -- Service表自身过滤条件
    AND s.paid = 'NY' 
    AND s.ymd BETWEEN 20240303 AND 20240311
    -- 核心：排除存在违规DIV的NBR
    AND NOT EXISTS (
        SELECT 1 
        FROM service s2 
        WHERE s2.nbr = s.nbr 
        AND s2.div IN ('AR91', 'AR10', 'AG55', 'AZ56', 'CZ12')
    )

更清晰的写法（先筛选合规NBR） #

WITH valid_nbrs AS (
    SELECT DISTINCT nbr
    FROM service
    WHERE NOT EXISTS (
        SELECT 1 
        FROM service s2 
        WHERE s2.nbr = service.nbr 
        AND s2.div IN ('AR91', 'AR10', 'AG55', 'AZ56', 'CZ12')
    )
)
SELECT DISTINCT
    s.nbr,
    s.model
FROM
    service s
JOIN process p ON p.nbr = s.nbr
JOIN valid_nbrs vn ON vn.nbr = s.nbr
WHERE
    p.unit = 'MC' 
    AND p.car = 'M' 
    AND p.bank = '1'
    AND p.paid IN ('NY', 'NJ')
    AND s.paid = 'NY' 
    AND s.ymd BETWEEN 20240303 AND 20240311

修正说明 #

1. 调整连接类型：用内连接替代左连接，确保只保留同时存在于Process和Service的有效记录。
2. 优化排除逻辑：NOT EXISTS子句会检查当前NBR在Service表中是否有任何违规DIV记录，只要存在就排除该NBR的所有数据。
3. 简化字段判断：length(s.ymd)=8是多余的——YMD字段定义为NUMBER(8,0)，本身就是8位数字，直接用数值范围判断即可。

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