咨询：查询员工表中值为'Y'的互斥列名的SQL解决方案

场景说明 #

employee表包含id、valid、invalid、non-scope字段，其中valid、invalid、non-scope为互斥列——同一行仅会有一列值为'Y'，其余为'N'或空值。需要输出id与对应状态列名的映射表。

方案1：通用CASE WHEN语句（兼容所有SQL方言） #

这是最通用的实现方式，几乎支持所有数据库系统：

SELECT 
    id,
    CASE
        WHEN valid = 'Y' THEN 'valid'
        WHEN invalid = 'Y' THEN 'invalid'
        -- 注意：带特殊字符的列名需用引号/反引号包裹，不同数据库语法略有差异
        WHEN "non-scope" = 'Y' THEN 'non-scope'
    END AS status_column
FROM employee;

• 适配多表关联：直接将逻辑嵌入关联查询即可，示例：

SELECT 
    e.id,
    CASE
        WHEN e.valid = 'Y' THEN 'valid'
        WHEN e.invalid = 'Y' THEN 'invalid'
        WHEN e."non-scope" = 'Y' THEN 'non-scope'
    END AS status_column,
    d.department_name
FROM employee e
JOIN department d ON e.department_id = d.id;

方案2：UNION ALL 行转列（适合扩展新增状态列） #

如果后续可能新增互斥状态列，这种方式更易维护：

SELECT id, 'valid' AS status_column
FROM employee
WHERE valid = 'Y'
UNION ALL
SELECT id, 'invalid' AS status_column
FROM employee
WHERE invalid = 'Y'
UNION ALL
SELECT id, 'non-scope' AS status_column
FROM employee
WHERE "non-scope" = 'Y';

• 新增状态列时，只需添加一个UNION ALL分支即可，无需修改现有逻辑。

方案3：数据库特定简化写法 #

MySQL

利用嵌套IF函数实现：

SELECT 
    id,
    IF(valid = 'Y', 'valid', 
       IF(invalid = 'Y', 'invalid', 'non-scope')) AS status_column
FROM employee;

PostgreSQL

结合COALESCE与CASE语句简化：

SELECT 
    id,
    COALESCE(
        CASE WHEN valid = 'Y' THEN 'valid' END,
        CASE WHEN invalid = 'Y' THEN 'invalid' END,
        CASE WHEN "non-scope" = 'Y' THEN 'non-scope' END
    ) AS status_column
FROM employee;

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