为MySQL中的多个字段选择出现的值数目

Question

使用MySQL。我有张桌子看起来像这样：

   rawID | a1a | a1b | a2a | a2b | a2c | ...
     1   |  2  |  0  |  0  |  0  |  2  | ...
     2   |  2  |  2  |  0  |  0  |  0  | ...
     3   |  0  |  1  |  1  |  1  |  2  | ...
     4   |  2  |  1  |  2  |  1  |  2  | ...

*字段的可能值在单独的“响应”表中预先定义，范围从0到2。

我想要一个结果，给出每个字段的每个值的出现情况。我见过类似的问题，但只有一个领域。

我希望我的结果是这样的：

   response | a1a | a1b | a2a | a2b | a2c | ...
      0     |  1  |  1  |  2  |  2  |  1  | ...
      1     |  0  |  2  |  1  |  2  |  0  | ...
      2     |  3  |  1  |  1  |  0  |  3  | ...

此表有每个字段的每个值(“响应”)发生的次数。

谢谢各位，

Answer 1

试试这个：

SELECT
  response,
  sum(a1a = response) a1aCount,
  sum(a1b = response) a1bCount,
  sum(a2a = response) a2aCount,
  sum(a2b = response) a2bCount,
  sum(a2c = response) a2cCount
FROM t, r
GROUP BY response

输出：

| RESPONSE | A1ACOUNT | A1BCOUNT | A2ACOUNT | A2BCOUNT | A2CCOUNT |
|----------|----------|----------|----------|----------|----------|
|        0 |        1 |        1 |        2 |        2 |        1 |
|        1 |        0 |        2 |        1 |        2 |        0 |
|        2 |        3 |        1 |        1 |        0 |        3 |

小提琴这里.

不管response表中的项数多少，这都是可行的。

Answer 2

我认为这应该有效(我们假设您的表名是"responses")：

SELECT 0 as response, SUM(r.a1a = 0) as a1a, SUM(r.a1b = 0) as a1b,
SUM(r.a2a = 0) as a2a, SUM(r.a2b = 0) as a2b FROM responses r
UNION
SELECT 1 as response, SUM(r.a1a = 1) as a1a, SUM(r.a1b = 1) as a1b, 
SUM(r.a2a = 1) as a2a, SUM(r.a2b = 1) as a2b FROM responses r
UNION
SELECT 2 as response, SUM(r.a1a = 2) as a1a, SUM(r.a1b = 2) as a1b,    
SUM(r.a2a = 2) as a2a, SUM(r.a2b = 2) as a2b FROM responses r