MySQL的：选择并在同一查询

Question

COUNT我有这两个表：

City表

CLUB表

我”什么m试图做的是选择相同的队列ry包含已发布俱乐部的所有城市（published字段设置为1）以及该城市中发布的俱乐部总数。

目前，我正在做两个步骤，但我想通过在一个查询中合并这些步骤来提高性能。

SELECT c.id, c.name, c.slug 
FROM city c, club cl 
WHERE c.id = cl.city_id 
AND (SELECT COUNT(*) 
     FROM club cl, city c 
     WHERE cl.city_id = c.id AND cl.published = 1) > 0 
GROUP BY c.id 

在此之后，我正在为每个城市查询以获取COUNT。

Answer 1

事情是这样的： -

SELECT city.id, city.name, city.slug, COUNT(club.id) AS club_count 
FROM city 
INNER JOIN club 
ON city.id = club.city_id 
WHERE club.published = 1 
GROUP BY city.id, city.name, city.slug 
HAVING club_count > 0