/*
SELECT countries.name AS country, COUNT(*) AS cities_num
FROM cities
INNER JOIN countries
ON countries.code = cities.country_code
GROUP BY country
ORDER BY cities_num DESC, country
LIMIT 9;
*/
SELECT name AS country,
-- Subquery
(SELECT count(*)
FROM cities
WHERE countries.code = cities.country_code) AS cities_num
FROM countries
ORDER BY cities_num DESC, country
LIMIT 9;
我期望子查询返回国家中所有城市的计数值,如果发生这种情况,我期望在较低的查询中出现错误,但它给我的结果与较高的查询相同,即每个国家的城市计数。
你所说的子查询实际上是一个经典的关联子查询。
SELECT name AS country,
-- Subquery
(SELECT count(*)
FROM cities
WHERE countries.code = cities.country_code) AS cities_num
FROM countries
ORDER BY cities_num DESC, country
LIMIT 9;
相关子查询是指对外部表中的每条记录执行一次的子查询。。您可以通过where条件来识别关联子查询,该条件将始终引用外部表中的列。
countries.code = cities.country_code
国家为外表,城市为内表。
如果您想使用子查询计算国家的所有城市,只需删除上述条件。