基于多变量组合的SQL Server 2016标识位生成需求

嘿，我来帮你搞定这个SQL Server 2016的需求！先把问题和数据理清楚，再给你靠谱的解决方案：

我们需要基于两张表的数据，给表B中的每个ID标记一个Flag：如果表A中存在任意一行的Column1和Column2同时出现在表B同一ID对应的SubID列表里，标记为1；否则标记为0。

表A（修正后）的结构与数据 #

原表A的创建语句存在问题（Column1设为主键但数据重复），我调整为组合主键确保数据能正常插入：

CREATE TABLE TableA(
    Column1 VARCHAR(5) NOT NULL,
    Column2 VARCHAR(5) NOT NULL,
    PRIMARY KEY (Column1, Column2) -- 组合主键避免重复行插入报错
);
INSERT INTO TableA(Column1,Column2) VALUES 
('0001M','80050'),
('0001M','80053'),
('0001M','80076'),
('0001T','0002T'),
('0001T','34800'),
('0001T','34802'),
('0001T','34804'),
('0001T','36000'),
('0001U','80500'),
('0001U','80502'),
('0001U','81105'),
('0001U','81106');

表B（修正后）的结构与数据 #

原表B的创建语句同样存在ID重复的问题，调整为ID和SubID的组合主键：

CREATE TABLE TableB(
    ID INTEGER NOT NULL,
    SubID VARCHAR(5) NOT NULL,
    PRIMARY KEY (ID, SubID) -- 组合主键确保同一ID下SubID不重复
);
INSERT INTO TableB(ID,SubID) VALUES 
(1,'0001M'),
(1,'80050'),
(1,'80053'),
(1,'12500'),
(2,'0001T'),
(2,'0002T'),
(2,'34800'),
(2,'36000'),
(2,'12506'),
(3,'80500'),
(3,'80502'),
(3,'81106');

关键规则说明 #

• ID=1标记1：表A中的(0001M,80050)和(0001M,80053)这两行的两个值，都能在ID=1的SubID列表里找到，满足条件
• ID=3标记0：表A中没有任何一行的Column1和Column2同时出现在ID=3的SubID中（ID=3的SubID都是表A的Column2值，没有对应的Column1），不满足条件

预期输出 #

ID | Flag
---|-----
1  | 1
2  | 1
3  | 0

SQL Server 2016解决方案 #

这里给你三种可行的方法，你可以根据数据量和性能需求选择：

方法一：使用EXISTS子查询（最直观） #

这种方法直接检查每个ID下是否存在符合条件的表A行，逻辑清晰：

SELECT DISTINCT
    b.ID,
    CASE 
        WHEN EXISTS (
            SELECT 1
            FROM TableA a
            WHERE EXISTS (SELECT 1 FROM TableB b1 WHERE b1.ID = b.ID AND b1.SubID = a.Column1)
              AND EXISTS (SELECT 1 FROM TableB b2 WHERE b2.ID = b.ID AND b2.SubID = a.Column2)
        ) THEN 1
        ELSE 0
    END AS Flag
FROM TableB b;

方法二：JOIN+分组统计 #

通过左连接匹配符合条件的表A行，再分组统计是否有匹配记录：

SELECT
    b.ID,
    CASE WHEN COUNT(a.Column1) > 0 THEN 1 ELSE 0 END AS Flag
FROM TableB b
LEFT JOIN TableA a 
    ON EXISTS (SELECT 1 FROM TableB b1 WHERE b1.ID = b.ID AND b1.SubID = a.Column1)
    AND EXISTS (SELECT 1 FROM TableB b2 WHERE b2.ID = b.ID AND b2.SubID = a.Column2)
GROUP BY b.ID;

方法三：拼接SubID后检查包含关系（适合小数据量） #

先把每个ID的SubID拼接成逗号分隔的字符串，再检查表A的两个值是否都在字符串里（SQL Server 2016用FOR XML PATH实现拼接，因为STRING_AGG是2017才支持的）：

WITH B_SubIDs AS (
    SELECT
        ID,
        STUFF((
            SELECT ',' + SubID 
            FROM TableB b2 
            WHERE b2.ID = b1.ID 
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS SubID_List
    FROM TableB b1
    GROUP BY ID
)
SELECT
    ID,
    CASE 
        WHEN EXISTS (
            SELECT 1
            FROM TableA a
            WHERE CHARINDEX(',' + a.Column1 + ',', ',' + SubID_List + ',') > 0
              AND CHARINDEX(',' + a.Column2 + ',', ',' + SubID_List + ',') > 0
        ) THEN 1
        ELSE 0
    END AS Flag
FROM B_SubIDs;

这三种方法都能得到你想要的结果，其中方法一在数据量较大时性能更优，因为EXISTS是短路求值，找到匹配项就停止查询。

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