SQL多列公共值高效统计：医疗库患者病症TOP10统计需求

核心思路 #

把分散在condition1到condition6的多列病症数据，通过纵向合并转换成单条病症的数据集，再统一统计频次并排序取前10，无需提前枚举所有病症类型。

通用SQL实现（支持绝大多数数据库） #

SELECT condition, COUNT(*) AS occurrence_count
FROM (
    -- 将6个病症列转成单列
    SELECT condition1 AS condition FROM patients
    UNION ALL
    SELECT condition2 AS condition FROM patients
    UNION ALL
    SELECT condition3 AS condition FROM patients
    UNION ALL
    SELECT condition4 AS condition FROM patients
    UNION ALL
    SELECT condition5 AS condition FROM patients
    UNION ALL
    SELECT condition6 AS condition FROM patients
) AS unpivoted_conditions
WHERE condition IS NOT NULL  -- 过滤未填病症的空值
GROUP BY condition
ORDER BY occurrence_count DESC
LIMIT 10;  -- 取排名前10的病症

关键细节说明

• 用UNION ALL而非UNION：UNION会自动去重，丢失重复的病症记录导致统计频次不准；UNION ALL保留所有原始数据，确保计数准确。
• 过滤空值：部分患者可能不足6种病症，对应列会是空值，必须排除避免统计无效数据。

数据库专属优化方案（可选） #

如果你的数据库支持UNPIVOT语法（如SQL Server、Oracle），可以用更简洁的写法：

SQL Server 版本

SELECT condition, COUNT(*) AS occurrence_count
FROM patients
UNPIVOT (
    condition FOR conditions IN (condition1, condition2, condition3, condition4, condition5, condition6)
) AS unpivoted_data
WHERE condition IS NOT NULL
GROUP BY condition
ORDER BY occurrence_count DESC
TOP 10;  -- SQL Server用TOP替代LIMIT

Oracle 版本

SELECT condition, COUNT(*) AS occurrence_count
FROM patients
UNPIVOT (
    condition FOR conditions IN (condition1, condition2, condition3, condition4, condition5, condition6)
) AS unpivoted_data
WHERE condition IS NOT NULL
GROUP BY condition
ORDER BY occurrence_count DESC
FETCH FIRST 10 ROWS ONLY;  -- Oracle用FETCH FIRST语法

方案优势 #

• 无需提前枚举所有30种左右的病症，自动覆盖所有列的所有值
• 仅需一次表扫描（或子查询处理），性能远优于多次单独统计每列再合并的暴力解法
• 代码易维护，后续新增病症列时，只需在合并逻辑中添加对应行即可

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