如何用SQL统计多列中相同数据的出现次数

源数据表 #

需求 #

统计上述表格中所有op0至op5列里各数据的出现次数，得到如下统计结果：

预期统计结果 #

实现SQL语句 #

SELECT op_value AS `all op`, COUNT(*) AS count
FROM (
    SELECT op0 AS op_value FROM your_table_name
    UNION ALL
    SELECT op1 AS op_value FROM your_table_name
    UNION ALL
    SELECT op2 AS op_value FROM your_table_name
    UNION ALL
    SELECT op3 AS op_value FROM your_table_name
    UNION ALL
    SELECT op4 AS op_value FROM your_table_name
    UNION ALL
    SELECT op5 AS op_value FROM your_table_name
) AS unpivoted
WHERE op_value IS NOT NULL AND op_value != ''
GROUP BY op_value
ORDER BY op_value;

说明 #

• 用UNION ALL将所有op列的行数据垂直拼接成单列，避免UNION自动去重导致统计错误
• 过滤空值（包括NULL和空字符串），确保统计有效数据
• 对拼接后的单列数据分组统计次数，最后按数据值排序

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