mysql - 获取同一表中两列之间的出现次数

Question

我有一个关于如何获取 MySQL 中两列之间的值的出现次数的问题。

id1 col1 col2
2    5      3
3    3      4
4    2      1
5    1      3
6    null   2

我怎样才能像下面这样得到两列之间出现的次数？

value     occurrence
3                3
1                2
2                2
4                1
5                1

Answer 1

您可以联合所有列，然后应用count聚合函数:

SELECT   val, COUNT(*) AS occurrence
FROM     (SELECT col1 AS val
          FROM   mytable
          UNION ALL
          SELECT col2 AS val
          FROM   mytable) x
GROUP BY val
ORDER BY occurrence DESC