如何针对同一ClassID与EmpID的多行记录，判断证书区域匹配并打标？

按ClassID维度，判断某一EmpID是否至少有一个StateCertArea与该ClassID对应的FedCertArea匹配，匹配则将IsInArea字段标记为1，否则为0。

背景说明 #

• 所有ClassID均包含FedCertArea字段
• 所有EmpID均拥有StateCertArea字段
• 核心目标：验证EmpID的StateCertArea是否与对应ClassID的任意FedCertArea一致

实现要求 #

尽量避免在CASE语句中嵌套SELECT，优先采用临时表关联查询的方式实现。

测试用SQL代码 #

DROP TABLE IF EXISTS #temptable
CREATE TABLE #temptable ( ClassID int, EmpID decimal(10,0), StateCertArea varchar(4), FedCertArea varchar(4))

INSERT INTO #temptable (ClassID, EmpID, StateCertArea, FedCertArea)
VALUES
( 572888, 77777, '228', '228'),
( 572888, 77777, '389', '389'),
( 572888, 77777, '374', '374'),
( 222555, 77777, '333', '999')

SELECT t.ClassID
     , t.EmpID
     , t.StateCertArea
     , t.FedCertArea      
     --, CASE WHEN 'EmployeeID has at least 1 StateCertArea in FedCertArea' THEN 1 ELSE 0 END AS IsInArea    
FROM #temptable AS t

预期结果 #

生成包含IsInArea字段的查询结果：

• ClassID为572888、EmpID为77777的所有记录，IsInArea值为1
• ClassID为222555、EmpID为77777的记录，IsInArea值为0

解决方案 #

方法1：临时表关联实现 #

先预存存在匹配关系的ClassID-EmpID对，再与原表关联标记结果：

-- 创建临时表存储已匹配的ClassID-EmpID组合
DROP TABLE IF EXISTS #MatchedPairs
CREATE TABLE #MatchedPairs (
    ClassID int,
    EmpID decimal(10,0)
)

INSERT INTO #MatchedPairs
SELECT DISTINCT ClassID, EmpID
FROM #temptable
WHERE StateCertArea = FedCertArea

-- 关联原表生成最终结果
SELECT
    t.ClassID,
    t.EmpID,
    t.StateCertArea,
    t.FedCertArea,
    CASE WHEN mp.ClassID IS NOT NULL THEN 1 ELSE 0 END AS IsInArea
FROM #temptable t
LEFT JOIN #MatchedPairs mp 
    ON t.ClassID = mp.ClassID 
    AND t.EmpID = mp.EmpID

方法2：窗口函数实现（更简洁） #

无需额外临时表，通过窗口函数在ClassID-EmpID分组内判断是否存在匹配项：

SELECT
    t.ClassID,
    t.EmpID,
    t.StateCertArea,
    t.FedCertArea,
    -- 分组内只要有一条匹配记录，MAX就会返回1
    MAX(CASE WHEN t.StateCertArea = t.FedCertArea THEN 1 ELSE 0 END) 
        OVER (PARTITION BY t.ClassID, t.EmpID) AS IsInArea
FROM #temptable t

两种方法都能满足需求，方法1严格遵循临时表关联的要求，方法2代码更简洁高效。

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