SQL如何在单个字段中列出所有判定为真的CASE语句结果

需求说明 #

现有代码已实现两个统计列的计算逻辑：

• fruit：累计apple、grapes字段值大于0的项数，值大于0代表存在匹配的水果类条目
• colors：累计red、blue字段值大于0的项数，值大于0代表存在匹配的颜色类条目

需要新增NewColumn字段，规则为：

• 若fruit值大于0，字段中写入Fruit
• 若colors值大于0，字段中写入Colors
• 两个字段都满足则同时写入两个值，单个满足则只写入对应值，都不满足则返回空

效果参考示例：

示例规则验证：Tom Brady对应的fruit列值为0，因此NewColumn中不会出现"Fruit"内容。

原有代码问题 #

提供的原始代码存在一处语法笔误：计算fruit值时重复写了WHEN关键字，对应片段为CASE WHEN WHEN grapes > 0，需要删除多余的WHEN才能正常运行。另外table是SQL保留关键字，作为表名使用时需要按对应数据库规则做转义。

调整后可运行代码 #

版本1：无嵌套直接计算（兼容绝大多数SQL数据库） #

SELECT
  *,
  -- 修正原笔误后计算fruit统计值
  (
    (CASE WHEN apple > 0 THEN 1 ELSE 0 END) 
    + (CASE WHEN grapes > 0 THEN 1 ELSE 0 END)
  ) AS fruit,
  -- 计算colors统计值
  (
    (CASE WHEN red > 0 THEN 1 ELSE 0 END) 
    + (CASE WHEN blue > 0 THEN 1 ELSE 0 END)
  ) AS colors,
  -- 生成目标NewColumn字段
  TRIM(BOTH ',' FROM CONCAT(
    CASE WHEN (CASE WHEN apple > 0 THEN 1 ELSE 0 END) + (CASE WHEN grapes > 0 THEN 1 ELSE 0 END) > 0 THEN 'Fruit,' ELSE '' END,
    CASE WHEN (CASE WHEN red > 0 THEN 1 ELSE 0 END) + (CASE WHEN blue > 0 THEN 1 ELSE 0 END) > 0 THEN 'Colors' ELSE '' END
  )) AS NewColumn
FROM `table`;

版本2：CTE预计算写法（可读性更高，推荐） #

先通过CTE提前算好fruit和colors的值，避免重复写相同的判断逻辑，后续维护更方便：

WITH base_calc AS (
  SELECT
    *,
    (
      (CASE WHEN apple > 0 THEN 1 ELSE 0 END) 
      + (CASE WHEN grapes > 0 THEN 1 ELSE 0 END)
    ) AS fruit,
    (
      (CASE WHEN red > 0 THEN 1 ELSE 0 END) 
      + (CASE WHEN blue > 0 THEN 1 ELSE 0 END)
    ) AS colors
  FROM `table`
)
SELECT
  *,
  TRIM(BOTH ',' FROM CONCAT(
    CASE WHEN fruit > 0 THEN 'Fruit,' ELSE '' END,
    CASE WHEN colors > 0 THEN 'Colors' ELSE '' END
  )) AS NewColumn
FROM base_calc;

逻辑说明 #

• 用CONCAT做字符串拼接，两个条件同时满足时会自动生成Fruit,Colors格式的内容
• 用TRIM函数去除首尾多余的逗号，避免单条件匹配时出现开头/结尾残留逗号的问题
• 如果使用PostgreSQL、Oracle等支持||拼接符的数据库，也可以将CONCAT替换为||写法，逻辑完全一致

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