基于条件与值输出列名的SQL实现问题

首先得指出你当前代码里的核心问题：你的每个SELECT语句里写了data=y and data=x，这是矛盾的——一行数据的data列不可能同时等于两个不同的值，所以这个条件永远不会满足，自然查不到任何结果。

下面给你几种可行的修正方案，你可以根据自己的数据库类型和列的数量来选择：

方案1：用EXISTS子查询（适合列数量较少的场景） #

这个方法逻辑最直观，针对每个列分别检查：是否存在data='x'且该列是'yes'的行，同时存在data='y'且该列是'yes'的行。

SELECT 'A' AS column_name
FROM world.city
WHERE EXISTS (SELECT 1 FROM world.city WHERE data = 'x' AND A = 'yes')
  AND EXISTS (SELECT 1 FROM world.city WHERE data = 'y' AND A = 'yes')

UNION ALL

SELECT 'B' AS column_name
FROM world.city
WHERE EXISTS (SELECT 1 FROM world.city WHERE data = 'x' AND B = 'yes')
  AND EXISTS (SELECT 1 FROM world.city WHERE data = 'y' AND B = 'yes')

UNION ALL

SELECT 'C' AS column_name
FROM world.city
WHERE EXISTS (SELECT 1 FROM world.city WHERE data = 'x' AND C = 'yes')
  AND EXISTS (SELECT 1 FROM world.city WHERE data = 'y' AND C = 'yes')

UNION ALL

SELECT 'D' AS column_name
FROM world.city
WHERE EXISTS (SELECT 1 FROM world.city WHERE data = 'x' AND D = 'yes')
  AND EXISTS (SELECT 1 FROM world.city WHERE data = 'y' AND D = 'yes');

方案2：用条件聚合（通用型，支持大多数数据库） #

通过聚合函数分别提取data='x'和data='y'时各列的值，再判断是否都是'yes'：

SELECT column_name
FROM (
    -- 检查列A
    SELECT
        CASE 
            WHEN MAX(CASE WHEN data = 'x' THEN A END) = 'yes' 
             AND MAX(CASE WHEN data = 'y' THEN A END) = 'yes' 
            THEN 'A' 
        END AS column_name
    FROM world.city
    
    UNION ALL
    
    -- 检查列B
    SELECT
        CASE 
            WHEN MAX(CASE WHEN data = 'x' THEN B END) = 'yes' 
             AND MAX(CASE WHEN data = 'y' THEN B END) = 'yes' 
            THEN 'B' 
        END AS column_name
    FROM world.city
    
    UNION ALL
    
    -- 检查列C
    SELECT
        CASE 
            WHEN MAX(CASE WHEN data = 'x' THEN C END) = 'yes' 
             AND MAX(CASE WHEN data = 'y' THEN C END) = 'yes' 
            THEN 'C' 
        END AS column_name
    FROM world.city
    
    UNION ALL
    
    -- 检查列D
    SELECT
        CASE 
            WHEN MAX(CASE WHEN data = 'x' THEN D END) = 'yes' 
             AND MAX(CASE WHEN data = 'y' THEN D END) = 'yes' 
            THEN 'D' 
        END AS column_name
    FROM world.city
) AS subquery
WHERE column_name IS NOT NULL;

方案3：用UNPIVOT（适合列数量多的场景，部分数据库支持） #

如果你的数据库支持UNPIVOT（比如SQL Server、Oracle），可以把列转成行，再分组统计，这样不用重复写每个列的逻辑：

SELECT DISTINCT column_name
FROM (
    -- 把列A/B/C/D转成行数据
    SELECT data, column_name, column_value
    FROM world.city
    UNPIVOT (
        column_value FOR column_name IN (A, B, C, D)
    ) AS unpivoted_data
) AS transformed
-- 筛选data是x或y，且值为yes的记录
WHERE data IN ('x', 'y') AND column_value = 'yes'
-- 分组后检查是否同时包含x和y的记录
GROUP BY column_name
HAVING COUNT(DISTINCT data) = 2;

内容的提问来源于stack exchange，提问作者S. Farooq