mysql - 如何通过一个查询在多列中查找所有相同的值

Question

如何对多列中的所有不同值进行汇总和排序？我有一个包含 3 列的表，并且想要计算这 3 列中出现的所有行的所有唯一值:worp1,worp2,worp3

对于一列，它正在使用此查询:

'''

SELECT 
    worp1, 
    COUNT(worp1) c
FROM
    logfile
GROUP BY worp1
order by c

'''

我想要的输出:

worp123  |count
T20      |20
T19      |18
20       |15
....
T11      |0

示例数据:

datum, speler, worp1,worp2,worp3
2019-05-07 23:50:46, 3, 1, T20, 0
2019-05-07 23:51:21, 1, 3, 7, 19
2019-05-07 23:51:34, 2, 5, T1, 20
2019-05-07 23:51:55, 3, 25, 25, D25
2019-05-07 23:52:28, 1, 1, 1, 12
2019-05-07 23:52:38, 2, 20, 20, 18
2019-05-07 23:53:03, 3, 1, 16, 2
2019-05-07 23:53:42, 1, 3, 19, 19
2019-05-07 23:53:53, 2, T5, 5, 5
2019-05-07 23:54:30, 3, 3, 10, 0
2019-05-07 23:54:59, 1, 19, T19, 20
2019-05-07 23:55:11, 2, 1, 5, 4
2019-05-07 23:55:30, 3, 17, 6, 25
2019-05-07 23:56:00, 1, 5, 7, 20
2019-05-07 23:56:07, 2, 20, 20, 20
2019-05-07 23:56:38, 3, 16, 15, 7
2019-05-07 23:57:11, 1, 7, 7, 19
2019-05-07 23:57:26, 2, 1, 5, 20
2019-05-07 23:57:43, 3, 2, 17, 17
2019-05-07 23:58:36, 1, T16, 4, 0
2019-05-07 23:58:43, 2, 4, 12, 20
2019-05-07 23:59:03, 3, 9, T20, 5
2019-05-07 23:59:40, 1, 0, 0, 0
2019-05-07 23:59:45, 2, 18, 20, 0
2019-05-08 00:00:14, 3, T1, 1, 20
2019-05-08 00:00:47, 1, 0, 0, 0
2019-05-08 00:00:54, 2, 6, 0, 0
2019-05-08 00:01:26, 3, T20, D14

谁能给我指出正确的方向吗？

Answer 1

他们可能是一种更有效的方法，但我认为这对您有用:

SELECT worp, SUM(cnt) FROM (
    SELECT worp1 AS worp, COUNT(worp1) AS cnt FROM logfile GROUP BY worp1
    UNION ALL
    SELECT worp2 AS worp, COUNT(worp2) AS cnt FROM logfile GROUP BY worp2
    UNION ALL
    SELECT worp3 AS worp, COUNT(worp3) AS cnt FROM logfile GROUP BY worp3
) a
GROUP BY worp