MySQL如何统计多列多行中各货币类型的总出现次数？

你要实现全表所有行列的货币出现次数统计，核心是把10个soldfor字段的宽表结构转成单列长表再分组统计，可直接使用以下SQL：

SELECT currency, COUNT(*) AS total
FROM (
    SELECT soldfor_1 AS currency FROM phpbb_topics WHERE forum_id=16 AND sold=1 AND soldfor_1 <> ''
    UNION ALL
    SELECT soldfor_2 AS currency FROM phpbb_topics WHERE forum_id=16 AND sold=1 AND soldfor_2 <> ''
    UNION ALL
    SELECT soldfor_3 AS currency FROM phpbb_topics WHERE forum_id=16 AND sold=1 AND soldfor_3 <> ''
    UNION ALL
    SELECT soldfor_4 AS currency FROM phpbb_topics WHERE forum_id=16 AND sold=1 AND soldfor_4 <> ''
    UNION ALL
    SELECT soldfor_5 AS currency FROM phpbb_topics WHERE forum_id=16 AND sold=1 AND soldfor_5 <> ''
    UNION ALL
    SELECT soldfor_6 AS currency FROM phpbb_topics WHERE forum_id=16 AND sold=1 AND soldfor_6 <> ''
    UNION ALL
    SELECT soldfor_7 AS currency FROM phpbb_topics WHERE forum_id=16 AND sold=1 AND soldfor_7 <> ''
    UNION ALL
    SELECT soldfor_8 AS currency FROM phpbb_topics WHERE forum_id=16 AND sold=1 AND soldfor_8 <> ''
    UNION ALL
    SELECT soldfor_9 AS currency FROM phpbb_topics WHERE forum_id=16 AND sold=1 AND soldfor_9 <> ''
    UNION ALL
    SELECT soldfor_10 AS currency FROM phpbb_topics WHERE forum_id=16 AND sold=1 AND soldfor_10 <> ''
) AS all_currency_records
GROUP BY currency
ORDER BY total DESC;

逻辑说明 #

• 内层通过UNION ALL将10个列的取值全部合并为单列currency，同时提前过滤空值和你原有业务条件的记录，避免统计无效空单元格
• 外层直接对货币类型分组计数，按次数倒序排序即可得到你需要的统计结果

如果你使用的是MySQL 8.0+/PostgreSQL等支持横向查询的数据库，可以用更简洁的写法避免重复代码：

SELECT currency, COUNT(*) AS total
FROM phpbb_topics,
LATERAL (
    VALUES (soldfor_1), (soldfor_2), (soldfor_3), (soldfor_4), (soldfor_5),
           (soldfor_6), (soldfor_7), (soldfor_8), (soldfor_9), (soldfor_10)
) AS temp(currency)
WHERE forum_id=16 AND sold=1 AND currency <> ''
GROUP BY currency
ORDER BY total DESC;

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