SQL 查询 - 从 CASE 计算新定义的值

Question

我在使用 SQL 查询时遇到问题。
我已经尝试了很多小时并在互联网上搜索，但无法完全找到我需要的东西。

我有 2 个表，它们通过一个 ID 作为主键和外键连接。
第二个表中的值都有一个特定的类别。第二个表中的每个值仅具有不同的类别。
我需要在查询中将其分类为新类别。现在多个值可能具有相同的类别。
所需的输出是这些新类别及其各自计数的列表，包括 0。

我尝试过与子表等不同的连接，但没有得到想要的结果。

不幸的是，我无法向您展示精确的表/查询，所以我试图复制这个问题(我希望这是可以理解的)

我的表:

meals:
| id | name | food_id |
+----+------+---------+ 
| 01 | Pete | 001     | 
| 02 | Anna | 002     |
| 03 | Jim  | 003     |
| 04 | John | 002     |
| 05 | Lucy | 003     |

food:
| id  | name   |
+-----+--------+    
| 001 | Apple  |    
| 002 | Banana | 
| 003 | Carrot | 
| 004 | Steak  | 

我的查询:

SELECT      food.foodtype           AS foodtype,
            COUNT(food.foodtype)    AS amount

FROM        (
            SELECT  food.id         AS id
                    CASE WHEN food.name = 'Apple'   THEN 'Fruit',
                         WHEN food.name = 'Banana'  THEN 'Fruit',
                         WHEN food.name = 'Carrot'  THEN 'Vegetable',
                         WHEN food.name = 'Steak'   THEN 'Meat',
                    END             AS foodtype
            FROM    food
            )                       AS food

INNER JOIN  meals
ON          meals.food_id = food.id

GROUP BY    food.foodtype

此查询的结果是一个包含所有使用的食物类型的表，但我还需要将未使用的食物类型显示为 0。

| foodtype  | amount |
+-----------+--------+
| Fruit     | 3      |
| Vegetable | 2      |

我需要的结果:

| foodtype  | amount |
+-----------+--------+
| Fruit     | 3      |
| Vegetable | 2      |
| Steak     | 0      |

我有另一个想法，但仍然不是我需要的结果:

SELECT      CASE WHEN food.name = 'Apple'   THEN 'Fruit',
                 WHEN food.name = 'Banana'  THEN 'Fruit',
                 WHEN food.name = 'Carrot'  THEN 'Vegetable',
                 WHEN food.name = 'Steak'   THEN 'Meat',
            END                     AS foodtype
            COUNT(meals.id)         AS amount

FROM        meals

RIGHT JOIN  food
ON          food.id = meals.food_id

GROUP BY    food.name

我的结果:

| foodtype  | amount |
+-----------+--------+
| Fruit     | 1      |
| Fruit     | 2      |
| Vegetable | 2      |
| Meat      | 0      |

我希望我需要什么是可以理解的，并且有人可以帮助我解决这个问题。

Answer 1

使用 LEFT JOIN并计算属于给定食物类型的膳食。您的原创 INNER JOIN导致与任何膳食不匹配的食物类型从结果集中消失。

SELECT food.foodtype AS foodtype,
       COUNT(meals.food_id) AS amount
FROM
(
    SELECT  food.id AS id,
            CASE WHEN food.name = 'Apple'   THEN 'Fruit'
                 WHEN food.name = 'Banana'  THEN 'Fruit'
                 WHEN food.name = 'Carrot'  THEN 'Vegetable'
                 WHEN food.name = 'Steak'   THEN 'Meat'
            END AS foodtype
    FROM food
) AS food
LEFT JOIN meals
    ON meals.food_id = food.id
GROUP BY food.foodtype