mysql - 在 MYSQL 中跨多个列选择 SUM(DISTINCT 值)

Question

我有一个包含 5 列的 MySQL 表来存储各种值。对于表中的每条记录，5 列存储一个值(大约 15 个中的一个)。这是结构的示例:

| COL1 | COL2 | COL3 |  COL4 |  COL5 |
|------|------|------|-------|-------|
| val1 | val3 | val8 | val11 | val14 |
| val2 | val3 | val5 |  val9 | val12 |
| val1 | val2 | val6 | val14 | val15 |
| val3 | val5 | val9 | val10 | val12 |
| val2 | val4 | val7 | val11 | val14 |

我想创建一个查询，对这 5 列中每一列的唯一值求和。结果应显示 val1、val2、val3 等在 5 列中出现的总次数。

理想情况下，结果的结构如下:

val  | total
_____________________

val1 |  34

val2 |  12

val3 |  23

val4 |  14

val5 |  21

etc  |  etc

在此先感谢您的帮助，非常感谢。

Answer 1

一种方式:SQL Fiddle

SELECT val,
       COUNT(*) AS total
FROM   (SELECT Col1 AS Val
        FROM   YourTable
        UNION ALL
        SELECT Col2 AS Val
        FROM   YourTable
        UNION ALL
        SELECT Col3 AS Val
        FROM   YourTable
        UNION ALL
        SELECT Col4 AS Val
        FROM   YourTable
        UNION ALL
        SELECT Col5 AS Val
        FROM   YourTable) AS T
GROUP  BY val 

或另一个:SQL Fiddle

SELECT CASE N
         WHEN 1 THEN Col1
         WHEN 2 THEN Col2
         WHEN 3 THEN Col3
         WHEN 4 THEN Col4
         WHEN 5 THEN Col5
       END AS Val,
       COUNT(*) AS total
FROM   YourTable
       CROSS JOIN (SELECT 1 AS N
                   UNION ALL
                   SELECT 2
                   UNION ALL
                   SELECT 3
                   UNION ALL
                   SELECT 4
                   UNION ALL
                   SELECT 5) AS T
GROUP  BY Val