我有这样的表
id | block1 | block2 | block3
-----------------------------------------------
1 | John | John |
2 | Mark | |
3 | | Frank | Frank
我如何计算表中有多少约翰、马克和弗兰克？
John - 2
Mark - 1
Frank - 2
我使用下面的查询，但仅适用于一列，我不知道如何对所有列产生相同的结果。我认为这是一个有用的查询，因为我阅读了可能是上个月或去年的结果
SELECT block1,
COUNT(*) AS total
FROM table
GROUP BY block1
ORDER BY total DESC;
谢谢（对不起我的英语）
您可以使用
UNION ALL取消表格透视，然后应用分组：
SELECT block, COUNT(*) AS 'total'
FROM (
SELECT block1 AS block
FROM mytable
UNION ALL
SELECT block2
FROM mytable
UNION ALL
SELECT block3
FROM mytable) AS t
GROUP BY block
单独选择列并使用UNION合并它们。然后COUNT他们。你可以试试这个——
SELECT col_block, COUNT(col_block) FROM (
(SELECT block1 col_block,
FROM table)
UNION ALL
(SELECT block2 col_block,
FROM table)
UNION ALL
(SELECT block3 col_block,
FROM table)) tbl
GROUP BY col_block