MySQL：同一查询中的SELECT和COUNT [英] MySQL: SELECT and COUNT in same query

问题描述

我有这两个表：

城市表

CLUB TABLE

我要做的是，使用相同的查询选择包含已发布俱乐部（已发布字段集的所有城市到1）以及该城市发布的俱乐部总数。

What I'm trying to do, is to select with the same query all cities that contain published clubs (published field set to 1) and the total of clubs published in that city.

目前，我分两个步骤进行操作，但是我想通过合并这些步骤来提高绩效

At the moment, I am doing it with two steps, but I would like to improve performance by merging these in just one query.

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

之后，我要查询每个

推荐答案

类似这样的东西：-

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

这篇关于MySQL：同一查询中的SELECT和COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！