如何编写SQL查询获取人口前三州中每州的人口前三城市？

你的原语句问题在于：它只是从人口前三的州里筛选出整体人口排名前三的城市，而非每个州各自的前三城市，所以结果不符合需求。

以下是正确的SQL查询语句（适配SQL Server，从你使用TOP语法推测是该环境）：

WITH TopStates AS (
    -- 获取人口前三的州的缩写
    SELECT st
    FROM State
    ORDER BY population DESC
    TOP 3
),
RankedCities AS (
    -- 为目标州的城市按州分组生成排名
    SELECT 
        c.st,
        c.cityname,
        c.population,
        ROW_NUMBER() OVER (PARTITION BY c.st ORDER BY c.population DESC) AS city_rank
    FROM City c
    INNER JOIN TopStates ts ON c.st = ts.st
)
-- 筛选每个州排名前三的城市
SELECT st, cityname, population
FROM RankedCities
WHERE city_rank <= 3
ORDER BY st, city_rank;

逻辑说明 #

1. TopStates：先通过子查询筛选出人口总量前三的州的缩写；
2. RankedCities：将City表与TopStates关联，用ROW_NUMBER()窗口函数按州分区（PARTITION BY c.st），并按城市人口降序排序，为每个城市生成其所在州内的排名；
3. 最后过滤出排名≤3的记录，按州和排名排序，即可得到每个目标州的前三人口城市。

如果是其他数据库（如PostgreSQL、Oracle），只需将TOP 3替换为FETCH FIRST 3 ROWS ONLY即可，核心逻辑保持一致。

内容的提问来源于stack exchange，提问作者a z