获取一个城市的客户数量

Question

我目前正在处理一个sql查询，目的很简单，我有一个客户表和一个采购表，它通过采购表中的外键链接。

模型是这样的：图像中的模型

我想我必须在sql中使用HAVING和COUNT函数，但我真的不知道如何正确编写查询才能得到这样的结果：

CITY	客户编号
巴黎	7
伦敦	3

SELECT C.CITY,COUNT(C.CUSTOMER_ID)CUSTOMER_COUNT
FROM CUSTOMER C
WHERE EXISTS
(
SELECT 1 FROM PURCHASE_ORDER AS RD 
WHERE C.CUSTOMER_ID=RD.CUSTOMER_ID
)
AND C.CITY IN('Paris','London')-- not sure if it is necessary
GROUP BY C.CITY

如果你只想要每个城市的客户数：

SELECT city, COUNT(*) n_customers
FROM "CUSTOMER"
GROUP BY city
ORDER BY n_customers DESC

如果你只想计算至少购买过一次的客户：

SELECT city, COUNT(*) n_customers
FROM (
SELECT t1.id, t1.city FROM "CUSTOMER" t1
INNER JOIN "PURCHASE_ORDER" t2 ON t1.id = t2.customer_id
GROUP BY t1.id
)
GROUP BY city
ORDER BY n_customers DESC;