如何从含5个EMPID列的表中提取符合规则的2列有效EMPID

需求说明 #

现有一张表，包含主键列Pkey和5个员工ID列EMPID1至EMPID5。标准员工ID为6位字母数字组合，但部分列的值不符合该标准。需要按照EMPID1→EMPID2→EMPID3→EMPID4→EMPID5的优先级，提取每条记录中前两个符合标准的有效员工ID，最终输出包含Pkey、EMPID11（第一个有效ID）、EMPID22（第二个有效ID）的结果集。

示例数据 #

预期输出 #

解决方案（SQL实现） #

方法1：行列转换+排序（适用于SQL Server、Oracle等支持UNPIVOT/PIVOT的数据库） #

WITH ValidEmpIDs AS (
    SELECT 
        Pkey,
        EmpID,
        ROW_NUMBER() OVER (PARTITION BY Pkey ORDER BY Priority) AS RN
    FROM (
        -- 逐个列筛选有效ID并标记优先级
        SELECT Pkey, EMPID1 AS EmpID, 1 AS Priority FROM YourTableName
        WHERE EMPID1 IS NOT NULL AND EMPID1 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]'
        UNION ALL
        SELECT Pkey, EMPID2 AS EmpID, 2 AS Priority FROM YourTableName
        WHERE EMPID2 IS NOT NULL AND EMPID2 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]'
        UNION ALL
        SELECT Pkey, EMPID3 AS EmpID, 3 AS Priority FROM YourTableName
        WHERE EMPID3 IS NOT NULL AND EMPID3 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]'
        UNION ALL
        SELECT Pkey, EMPID4 AS EmpID, 4 AS Priority FROM YourTableName
        WHERE EMPID4 IS NOT NULL AND EMPID4 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]'
        UNION ALL
        SELECT Pkey, EMPID5 AS EmpID, 5 AS Priority FROM YourTableName
        WHERE EMPID5 IS NOT NULL AND EMPID5 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]'
    ) AS UnpivotedData
)
-- 转换回列格式，取前两个有效ID
SELECT 
    Pkey,
    MAX(CASE WHEN RN = 1 THEN EmpID END) AS EMPID11,
    MAX(CASE WHEN RN = 2 THEN EmpID END) AS EMPID22
FROM ValidEmpIDs
GROUP BY Pkey
ORDER BY Pkey;

方法2：嵌套CASE语句（通用写法，适配所有支持CASE的数据库） #

SELECT
    Pkey,
    -- 获取第一个有效ID
    CASE
        WHEN EMPID1 IS NOT NULL AND EMPID1 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID1
        WHEN EMPID2 IS NOT NULL AND EMPID2 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID2
        WHEN EMPID3 IS NOT NULL AND EMPID3 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID3
        WHEN EMPID4 IS NOT NULL AND EMPID4 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID4
        WHEN EMPID5 IS NOT NULL AND EMPID5 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID5
        ELSE NULL
    END AS EMPID11,
    -- 获取第二个有效ID
    CASE
        -- 第一个有效ID是EMPID1时，从EMPID2-EMPID5筛选
        WHEN EMPID1 IS NOT NULL AND EMPID1 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN
            CASE
                WHEN EMPID2 IS NOT NULL AND EMPID2 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID2
                WHEN EMPID3 IS NOT NULL AND EMPID3 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID3
                WHEN EMPID4 IS NOT NULL AND EMPID4 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID4
                WHEN EMPID5 IS NOT NULL AND EMPID5 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID5
                ELSE NULL
            END
        -- 第一个有效ID是EMPID2时，从EMPID3-EMPID5筛选
        WHEN EMPID2 IS NOT NULL AND EMPID2 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN
            CASE
                WHEN EMPID3 IS NOT NULL AND EMPID3 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID3
                WHEN EMPID4 IS NOT NULL AND EMPID4 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID4
                WHEN EMPID5 IS NOT NULL AND EMPID5 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID5
                ELSE NULL
            END
        -- 第一个有效ID是EMPID3时，从EMPID4-EMPID5筛选
        WHEN EMPID3 IS NOT NULL AND EMPID3 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN
            CASE
                WHEN EMPID4 IS NOT NULL AND EMPID4 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID4
                WHEN EMPID5 IS NOT NULL AND EMPID5 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID5
                ELSE NULL
            END
        -- 第一个有效ID是EMPID4时，检查EMPID5
        WHEN EMPID4 IS NOT NULL AND EMPID4 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN
            CASE WHEN EMPID5 IS NOT NULL AND EMPID5 LIKE '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]' THEN EMPID5 ELSE NULL END
        -- 第一个有效ID是EMPID5时，无第二个有效ID
        ELSE NULL
    END AS EMPID22
FROM YourTableName
ORDER BY Pkey;

补充说明 #

• 正则匹配：如果数据库支持正则表达式（如MySQL用REGEXP '^[A-Z0-9]{6}$'，PostgreSQL用~ '^[A-Z0-9]{6}$'），可替换LIKE语句为更简洁的正则写法。
• 替换代码中的YourTableName为实际表名。

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