mysql - 我的 table 组织不当的另一个问题

Question

我有一个这样的表设置:

fruit1 | fruit2 | fruit3 | fruit4 | number1 | number2 | number3 | number4
--------------------------------------------------------------------------
apple  | orange | banana |  berry |    5    |    2    |    1    |    4   
orange | banana | apple  |  berry |    3    |    2    |    5    |    2
berry  | banana | orange |  apple |    1    |    2    |    5    |    2

我需要一个 MySQL 查询来计算每个水果与每个数字的次数。所以，它最终会是这样的:

Fruit | 5's | 4's | 3's | 2's | 1's 
-----------------------------------
Apple | 2   | 0   | 0   | 1   | 0 
Banana| 0   | 0   | 1   | 1   | 1
...

JW 帮了我很多，但我不知道如何从 JW 的解决方案到我现在需要的东西。
Need help figuring out MySQL query to count if a certain number

Answer 1

由于您在评论中提到最大值为 5，因此这是一个使用您之前问题中的 case 的转换版本，您希望在此处使用它，

SELECT fruit,
       SUM(CASE WHEN num = 5 THEN NumberOfInstance ELSE 0 END) `5's`,
       SUM(CASE WHEN num = 4 THEN NumberOfInstance ELSE 0 END) `4's`,
       SUM(CASE WHEN num = 3 THEN NumberOfInstance ELSE 0 END) `3's`,
       SUM(CASE WHEN num = 2 THEN NumberOfInstance ELSE 0 END) `2's`,
       SUM(CASE WHEN num = 1 THEN NumberOfInstance ELSE 0 END) `1's`
FROM
      (
        SELECT fruit, num, COUNT(*) NumberOfInstance
        FROM 
        (
          SELECT fruit1 fruit, number1 num FROM table1
          UNION ALL
          SELECT fruit2 fruit, number2 num FROM table1
          UNION ALL
          SELECT fruit3 fruit, number3 num FROM table1
          UNION ALL
          SELECT fruit4 fruit, number4 num FROM table1
        ) s 
        GROUP BY fruit, num
      ) s
GROUP BY fruit;

• SQLFiddle Demo Link