mysql - 在 MySQL 中一次汇总多列

Question

我有一些数据(约 70,000 行)，其格式与下面类似。

+-----------+-----+-----+----+-----------+
|    ID     |  A  |  B  | C  | Whatever  |
+-----------+-----+-----+----+-----------+
| 1banana   |  42 |   0 |  2 | Um        |
| fhqwhgads | 514 |   6 |  9 | Nevermind |
| 2banana   |  69 |  42 |  0 | NULL      |
| pears     |  18 |  96 |  2 | 8.8       |
| zubat2    |  96 |   2 | 14 | "NULL"    |
+-----------+-----+-----+----+-----------+

我想制作一个输出表来计算每个数字在三列中的任何一列中出现的次数，例如:

+--------+---------+---------+---------+-----+
| Number | A count | B count | C count | sum |
+--------+---------+---------+---------+-----+
|      0 |       0 |       1 |       1 |   2 |
|      2 |       0 |       1 |       2 |   3 |
|      6 |       0 |       1 |       0 |   1 |
|      9 |       0 |       0 |       1 |   1 |
|     14 |       0 |       0 |       1 |   1 |
|     18 |       1 |       0 |       0 |   1 |
|     42 |       1 |       1 |       0 |   2 |
|     69 |       1 |       0 |       0 |   1 |
|     96 |       1 |       1 |       0 |   2 |
|    514 |       1 |       0 |       0 |   1 |
+--------+---------+---------+---------+-----+

(在我的实际使用中，输入表中的行数至少是查询结果中的行数的 10 倍)

查询是否为这 3 列中不存在的数字返回一行零并不那么重要，因为缺少不同的总和列(尽管我的偏好是它确实有总和)列和不在任何列中的数字均被排除)。

<小时/>

目前，我正在使用以下查询来获取未分组的数据:

SELECT * #Number, COUNT(DISTINCT A), COUNT(DISTINCT B), COUNT(DISTINCT C)
FROM
    ( # Generate a list of numbers to try
    SELECT @ROW := @ROW + 1 AS `Number`
    FROM DataTable t
    join (SELECT @ROW := -9) t2
    LIMIT 777 # None of the numbers I am interested in should be greater than this
    ) AS NumberList
INNER JOIN DataTable ON
    Number = A
    OR Number = B
    OR Number = C
    #WHERE <filters on DataTable columns to speed things up>
#WHERE NUMBER = 10 # speed things up
#GROUP BY Number

上面的查询保留了代码中注释掉的部分，返回一个与数据表类似的表，但按匹配的条目编号排序。我想将所有以相同 Number 开头的行分组在一起，并将查询结果的“数据”列中的值作为 Number 的次数> 出现在DataTable的相应列中。

当我取消注释分组语句(并从 SELECT 语句中删除 *)时，我可以获得每个 Number 的行数code> 出现在(对于所需输出的 ​​sum 列很有用)。但是，它并没有给出 Number 与每个数据列匹配的实际总次数:我只获得了找到 Number 的行数的三份副本。 如何让分组按每个实际列而不是匹配行的总数？

<小时/>

此外，您可能已经注意到我有一些关于加快速度的评论。此查询慢，因此我添加了几个过滤器，以便测试它运行得更快。我非常希望有某种方法可以使其快速运行，以便将查询结果从完整集发送到新表并不是重用此数据的唯一合理方法，因为我希望能够出于非性能原因，尝试使用 DataTable 上的过滤器。 是否有更好的方法来构建整个查询，使其运行得更快？

Answer 1

我认为您想使用union all然后聚合来取消透视:

select number, sum(a) as a, sum(b) as b, sum(c) as c, count(*) as `sum`
from ((select a as number, 1 as a, 0 as b, 0 as c from t
      ) union all
      (select b, 0 as a, 1 as b, 0 as c from t
      ) union all
      (select c, 0 as a, 0 as b, 1 as c from t
      )
     ) abc
group by number
order by number;