技术问询：查找仅含register_nr=5且无register_nr=6的unit_nr

需求：从符合unit_type=58、input_nr IN (1,2)、有效时间不为空等基础条件的记录中，找出仅拥有register_nr=5的unit_nr，排除同时存在register_nr=6的unit_nr。

方法一：分组聚合筛选 #

先获取所有符合条件的register_nr=5和6的记录，再通过分组筛选出仅包含5的unit_nr：

WITH unit_registers AS (
    SELECT 
        ami.unit_nr,
        CR.register_nr
    FROM nw_metering_connection@AMIAMI AMI
    LEFT JOIN NW_UNIT_CONFIG NUC ON NUC.UNIT_NR = AMI.UNIT_NR
    LEFT JOIN CFG_REGISTER CR ON CR.CONFIGURATION_ID = NUC.CONFIGURATION_ID
    WHERE 
        ami.unit_type = 58 
        AND ami.input_nr IN (1,2) 
        AND ami.valid_until IS NULL 
        AND nuc.valid_until IS NULL 
        AND CR.REGISTER_TYPE = 8 
        AND CR.register_nr IN (5,6)
)
SELECT 
    ami.unit_type,
    ami.unit_nr, 
    nwmp.metering_point_id,
    CR.register_name 
FROM nw_metering_connection@AMIAMI AMI
LEFT JOIN NW_METERING_POINT@amiami nwmp ON nwmp.internal_metering_point_id = ami.internal_metering_point_id
LEFT JOIN NW_UNIT_CONFIG NUC ON NUC.UNIT_NR = AMI.UNIT_NR
LEFT JOIN CFG_CONFIGURATION CFG ON nuc.configuration_id = CFG.CONFIGURATION_ID
LEFT JOIN CFG_REGISTER CR ON CR.CONFIGURATION_ID = NUC.CONFIGURATION_ID
WHERE 
    ami.unit_type = 58 
    AND ami.input_nr IN (1,2) 
    AND ami.valid_until IS NULL 
    AND nuc.valid_until IS NULL 
    AND CR.REGISTER_TYPE = 8 
    AND CR.register_nr = 5
    AND ami.unit_nr IN (
        SELECT unit_nr 
        FROM unit_registers
        GROUP BY unit_nr
        HAVING COUNT(DISTINCT register_nr) = 1 AND MAX(register_nr) = 5
    );

方法二：用NOT EXISTS直接排除 #

先筛选出register_nr=5的记录，同时排除存在同unit_nr且register_nr=6的情况，逻辑更直观：

SELECT 
    ami.unit_type,
    ami.unit_nr, 
    nwmp.metering_point_id,
    CR.register_name 
FROM nw_metering_connection@AMIAMI AMI
LEFT JOIN NW_METERING_POINT@amiami nwmp ON nwmp.internal_metering_point_id = ami.internal_metering_point_id
LEFT JOIN NW_UNIT_CONFIG NUC ON NUC.UNIT_NR = AMI.UNIT_NR
LEFT JOIN CFG_CONFIGURATION CFG ON nuc.configuration_id = CFG.CONFIGURATION_ID
LEFT JOIN CFG_REGISTER CR ON CR.CONFIGURATION_ID = NUC.CONFIGURATION_ID
WHERE 
    ami.unit_type = 58 
    AND ami.input_nr IN (1,2) 
    AND ami.valid_until IS NULL 
    AND nuc.valid_until IS NULL 
    AND CR.REGISTER_TYPE = 8 
    AND CR.register_nr = 5
    AND NOT EXISTS (
        SELECT 1
        FROM nw_metering_connection@AMIAMI AMI_sub
        JOIN NW_UNIT_CONFIG NUC_sub ON NUC_sub.UNIT_NR = AMI_sub.UNIT_NR
        JOIN CFG_REGISTER CR_sub ON CR_sub.CONFIGURATION_ID = NUC_sub.CONFIGURATION_ID
        WHERE 
            AMI_sub.unit_nr = AMI.unit_nr
            AND AMI_sub.unit_type = 58
            AND AMI_sub.input_nr IN (1,2)
            AND AMI_sub.valid_until IS NULL
            AND NUC_sub.valid_until IS NULL
            AND CR_sub.REGISTER_TYPE = 8
            AND CR_sub.register_nr = 6
    );

两种方法都能满足需求，NOT EXISTS的写法在多数数据库中性能更优，建议优先使用，前提是相关字段（如unit_nr、register_nr）有合适的索引。

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