如何为SQL表生成基于账户区域匹配的Yes/No伪列

问题背景 #

需要为表test_18Nov的所有行生成一个值为Yes/No的伪列，规则如下：

• 若某account_id的所有新源区域（Region_new_source）都存在于该账户的legacy区域（Region_legacy_file）列表中，伪列显示No
• 若该账户存在至少一个新源区域不在legacy区域列表中，该账户所有行的伪列显示Yes

表结构与测试数据 #

create table test_18Nov ( 
    account_id nvarchar(12),
    account_name nvarchar(25),
    zip_legacy_file nvarchar(5),
    Region_legacy_file nvarchar(30),
    zip_new_source nvarchar(5),
    Region_new_source nvarchar(30)
);

INSERT INTO test_18Nov VALUES ('S1018', 'John Smith',       '32221', 'R087-Jacksonville',   '33803', 'R026-Lakeland');
INSERT INTO test_18Nov VALUES ('S1018', 'John Smith',       '33606', 'R011-Tampa',          '32220', 'R087-Jacksonville');
INSERT INTO test_18Nov VALUES ('S1018', 'John Smith',       '33803', 'R026-Lakeland',       '33606', 'R011-Tampa');
INSERT INTO test_18Nov VALUES ('AC054', 'David Thompson',   '33606', 'R011-Tampa',          '32205', 'R087-Jacksonville');
INSERT INTO test_18Nov VALUES ('AC054', 'David Thompson',   '33870', 'R058-Sebring',        '33606', 'R011-Tampa');
INSERT INTO test_18Nov VALUES ('AC054', 'David Thompson',   '33610', 'R011-Tampa',          '33870', 'R058-Sebring');
INSERT INTO test_18Nov VALUES ('AC077', 'Stacey Leigh',     '34950', 'R043-Fort Pierce',    '34982', 'R043-Fort Pierce');
INSERT INTO test_18Nov VALUES ('AC077', 'Stacey Leigh',     '33610', 'R011-Tampa',          '34950', 'R043-Fort Pierce');

尝试过的方法 #

• 使用NOT EXISTS子句：仅能返回新源区域不在legacy区域列表的行，无法为账户所有行统一标记
• 尝试CASE WHEN EXISTS：未成功实现需求

可行解决方案 #

方案一：用CTE预筛选账户 #

先通过CTE找出所有存在不符合条件（新源区域不在legacy列表）的账户，再关联主表统一标记：

WITH account_move_flag AS (
    -- 筛选出存在新源区域不在自身legacy列表的账户
    SELECT DISTINCT t1.account_id
    FROM test_18Nov t1
    WHERE NOT EXISTS (
        SELECT 1
        FROM test_18Nov t2
        WHERE t2.account_id = t1.account_id
          AND t2.Region_legacy_file = t1.Region_new_source
    )
)
SELECT 
    t.account_id,
    t.account_name,
    t.Region_legacy_file,
    t.Region_new_source,
    -- 根据是否在筛选出的账户列表中标记Yes/No
    CASE WHEN amf.account_id IS NOT NULL THEN 'Yes' ELSE 'No' END AS [Will the account move?]
FROM test_18Nov t
LEFT JOIN account_move_flag amf ON t.account_id = amf.account_id
ORDER BY t.account_id;

方案二：用窗口函数直接计算 #

通过窗口函数对每个账户的行进行判断，统一生成标记：

SELECT 
    account_id,
    account_name,
    Region_legacy_file,
    Region_new_source,
    CASE 
        -- 若账户内存在至少一个不符合条件的行，标记为Yes
        WHEN MAX(CASE 
                    WHEN NOT EXISTS (
                        SELECT 1 
                        FROM test_18Nov t2 
                        WHERE t2.account_id = t1.account_id 
                          AND t2.Region_legacy_file = t1.Region_new_source
                    ) THEN 1 
                    ELSE 0 
                 END) OVER (PARTITION BY account_id) = 1 
        THEN 'Yes' 
        ELSE 'No' 
    END AS [Will the account move?]
FROM test_18Nov t1
ORDER BY account_id;

说明 #

两种方案都能实现需求：

• 方案一先预筛选账户，数据量大时性能更优
• 方案二用窗口函数，逻辑更紧凑，无需额外关联

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