如何修改SQL查询获取各州对应城市名称及最新人口密度（解决分组报错）

需要实现SQL查询，获取每个州对应的一个城市名称及其人口密度，且使用城市的最新人口数据。当前子查询能正确获取城市最新人口数据，但主查询执行时返回not a group by expression错误：

• 移除s.name时查询可正常运行，但无法获取城市名称；
• 若将s.name加入GROUP BY子句，则会返回该州下所有城市的数据，不符合需求。

原查询代码 #

SELECT s.name, MAX(ROUND(p.population / s.area, 2)), e.encompasses_state
  FROM city s
  LEFT JOIN state e
    ON s.code = e.code
  LEFT JOIN citypopulations p
    ON p.code = s.code
 WHERE p.rowid IN (SELECT MAX(rowid) KEEP ( DENSE_RANK LAST ORDER BY year, population )
                     FROM citypopulations px
                    WHERE s.code = px.code)
 GROUP BY e.encompasses_state;

相关表结构及数据 #

City表 #

State表 #

Citypop表 #

核心思路是先通过窗口函数筛选出每个城市的最新人口记录，再关联城市、州表计算密度，最后按需按州输出对应城市数据。

修改后查询（适配单州单城市场景） #

WITH latest_city_pop AS (
    -- 筛选每个城市的最新人口记录：优先按年份降序，同年份按人口降序，最后按rowid降序
    SELECT 
        px.code,
        px.pop AS population,
        ROW_NUMBER() OVER (PARTITION BY px.code ORDER BY px.year DESC, px.pop DESC, px.rowid DESC) AS rn
    FROM citypopulations px
),
city_density AS (
    -- 关联计算每个城市的最新人口密度
    SELECT 
        s.name AS city_name,
        ROUND(lcp.population / s.area, 2) AS density,
        e.name AS state_name
    FROM city s
    JOIN state e ON s.code = e.code
    JOIN latest_city_pop lcp ON s.code = lcp.code
    WHERE lcp.rn = 1
)
-- 直接输出每个州对应的城市数据
SELECT state_name, city_name, density FROM city_density;

多州多城市场景适配（按规则取一个城市） #

如果一个州对应多个城市，需要指定规则（比如取人口密度最高的），可调整最后一步查询：

WITH latest_city_pop AS (
    SELECT 
        px.code,
        px.pop AS population,
        ROW_NUMBER() OVER (PARTITION BY px.code ORDER BY px.year DESC, px.pop DESC, px.rowid DESC) AS rn
    FROM citypopulations px
),
city_density AS (
    SELECT 
        s.name AS city_name,
        ROUND(lcp.population / s.area, 2) AS density,
        e.name AS state_name
    FROM city s
    JOIN state e ON s.code = e.code
    JOIN latest_city_pop lcp ON s.code = lcp.code
    WHERE lcp.rn = 1
)
-- 按州分组，取密度最高的城市
SELECT 
    state_name,
    MAX(city_name) KEEP (DENSE_RANK FIRST ORDER BY density DESC) AS city_name,
    MAX(density) AS population_density
FROM city_density
GROUP BY state_name;

逻辑说明 #

1. 用ROW_NUMBER()窗口函数替代原有的子查询，更直观地筛选每个城市的最新人口记录；
2. 先完成单条记录的关联与密度计算，再处理按州的聚合逻辑，避免了原查询中GROUP BY的字段冲突问题；
3. 保留了原需求中“最新人口数据”的筛选规则，同时灵活适配不同的城市选取规则。

内容的提问来源于stack exchange，提问作者Vin rich