mysql - SQL 新手 - 需要查询方面的帮助

Question

表 1:食物

id name
-------
1 Hot-dog
2 Sandwich
3 Apple

表 2:报告

id food_id date
----------------
1     1    2010-01-01
2     1    2010-02-01
3     2    2011-02-01

我如何设计一个查询以使用名为 numReports 的变量选择食物表中的所有食物，该变量是该食物在报告中的所有报告的总数？

我一直在尝试:

SELECT 
    food.id AS foodId, 
    food.name AS foodName,
    count(
        SELECT * FROM reports WHERE reports.food_id=food.id
        ) AS numReports
FROM 
    food

输出:

id name numReports
-------------------
1 Hot-dog  2
2 Sandwich 1
3 Apple    0

Answer 1

SELECT food.name,
       COUNT(*) as food_count
  FROM reports 
       INNER JOIN food ON reports.food_id = food.id
  GROUP BY food.name;