mysql - 如何在 MySQL SELECT 结果集中包含自定义计数？

Question

我有以下两个表:

TABLE: area
*City_ID   *Number   Name
--------   -------   ----
SUR        1         Fleetwood
SUR        2         Whalley
SUR        3         Guildford
SUR        4         Newton
SUR        5         Cloverdale
SUR        6         South Surrey
ABB        1         Abbotsford East
ABB        2         Abbotsford West
ABB        3         Aberdeen
ABB        4         Bradner
ABB        5         Central Abbotsford
ABB        6         Matsqui
ABB        7         Poplar
ABB        8         Sumas Mountain
ABB        9         Sumas Prairie

TABLE: city
*ID        Name
---        ----
SUR        Surrey
ABB        Abbotsford
LAN        Langley

使用以下语句:

SELECT DISTINCT area.City_ID, city.Name
FROM area
INNER JOIN city
WHERE area.City_ID = city.ID

我得到:

SELECT:
City_ID    city.Name
-------    ---------
SUR        Surrey
ABB        Abbotsford

但是我如何选择以下内容:

SELECT:
City_ID    city.Name    area_COUNT
-------    ---------    ----------
SUR        Surrey       6
ABB        Abbotsford   9

其中 area_COUNT 是每个相应 City_ID 的 area 中的行数？

Answer 1

使用GROUP BY而不是DISTINCT:

SELECT city.City_ID, city.Name, COUNT(*)
FROM area
INNER JOIN city
WHERE area.City_ID = city.ID
GROUP BY city.ID