如何在 SQL 中制作二维计数表

Question

我有一个包含多个字段的表，我想输出一个表，其中包含其中两个字段的所有组合的计数。例如：

Table Computers
ID  | CPU Type  | GPU Type
---------------------------
1   | CPU A     | GPU A
2   | CPU A     | GPU B
3   | CPU B     | GPU C
4   | CPU B     | GPU A
5   | CPU C     | GPU B
6   | CPU D     | GPU C
7   | CPU D     | GPU B
8   | CPU D     | GPU B
9   | CPU E     | GPU E
10  | CPU A     | GPU A

基本上，我想做的是创建一个二维计数表，在该表的每个条目中，有多少台计算机满足机器人的行和列条件：

        | GPU A | GPU B | GPU C | GPU D | GPU E |
-------------------------------------------------
CPU A   |   2   |   1   |   0   |   0   |   0   |
CPU B   |   1   |   0   |   1   |   0   |   0   |
CPU C   |   0   |   1   |   0   |   0   |   0   |
CPU D   |   0   |   2   |   1   |   0   |   0   |
CPU E   |   0   |   0   |   0   |   0   |   1   |

这两个字段中的每一个都是对应表中的外键，并且有大量条目，因此没有机会对所有可能的值进行硬编码。

如何在 MySQL 中执行此查询。有什么想法吗？

谢谢。

Answer 1

由于这可以是任意数量的 GPU，但 SQL 只能处理返回一组已知列的查询，因此基本上有两个选项：

1. 通过一个查询获取所有 GPU。然后动态构建一个 SQL 查询（在 PHP、Java 或任何你正在使用的查询中）为每个 GPU 返回一列。
2. 按行获取数据。使用您的应用程序（用PHP，Java或其他方式编写）从这些数据构建网格。

我更喜欢第二种选择。所需的查询很简单：

select cpu_type, gpu_type, count(*)
from computers
group by cpu_type, gpu_type
order by cpu_type, gpu_type;

（AFAIK MySQL不需要排序by子句，因为它保证根据GROUP BY子句对记录进行排序。但是，这是非标准行为，可能会在一段时间内更改。最好习惯于正确使用ORDER BY，当您希望对结果进行排序时。

Answer 2

使用 Conditional Aggregate 计算每个GPU Type。

select `CPU Type`, 
count(case when `GPU Type` = 'GPU A' then 1 end) `GPU A`,
count(case when `GPU Type` = 'GPU B' then 1 end) `GPU B`,
count(case when `GPU Type` = 'GPU C' then 1 end) `GPU C`,
count(case when `GPU Type` = 'GPU D' then 1 end) `GPU D`,
count(case when `GPU Type` = 'GPU E' then 1 end) `GPU E`
from yourtable 
group by `CPU Type`