SQLAlchemy中Group By与Count的使用疑问：如何查询各国拥有最多记录的城市

问题原因分析 #

你的查询犯了一个典型的SQL分组逻辑错误：当你用group_by(hotel.columns.country)仅对国家分组时，却在query()中直接选择了hotel.columns.city——这个字段既不在GROUP BY子句里，也没有被聚合函数（比如func.any_value()）包裹。

在SQLite这种允许非标准GROUP BY语法的数据库中，它会返回每个国家分组里的任意一条城市记录（通常是表中该国家的第一条城市数据），而func.count(hotel.columns.city)计算的是整个国家分组的总记录数，这就导致你看到的count是国家总条数，城市却只是随机的一个，完全不符合“每个国家记录最多的城市”的需求。

正确实现方案 #

要实现目标，我们需要先统计每个国家下每个城市的记录数，再筛选出每个国家中记录数最大的城市。下面提供两种常用的实现方式：

方法一：子查询关联法

先通过子查询计算每个国家-城市的计数，再找出每个国家的最大计数，最后关联两个结果得到目标数据：

from sqlalchemy import create_engine, MetaData, Table, Column, String, func
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import select

engine = create_engine('sqlite:///hotel.db', echo=True)
meta = MetaData()
hotel = Table(
    'hotel', meta,
    Column('country', String),
    Column('city', String),
    Column('name', String),
)
Session = sessionmaker(bind=engine)
session = Session()

# 子查询1：统计每个国家每个城市的记录数
city_count_subq = select(
    hotel.c.country,
    hotel.c.city,
    func.count(hotel.c.name).label('city_count')
).group_by(hotel.c.country, hotel.c.city).subquery()

# 子查询2：找出每个国家的最大城市记录数
max_count_subq = select(
    city_count_subq.c.country,
    func.max(city_count_subq.c.city_count).label('max_count')
).group_by(city_count_subq.c.country).subquery()

# 关联子查询，得到每个国家记录最多的城市
result = session.query(
    city_count_subq.c.country,
    city_count_subq.c.city,
    city_count_subq.c.city_count
).join(
    max_count_subq,
    (city_count_subq.c.country == max_count_subq.c.country) &
    (city_count_subq.c.city_count == max_count_subq.c.max_count)
).all()

print(result)

方法二：窗口函数法（更简洁推荐）

利用row_number()窗口函数，按国家分组、城市记录数降序排序，直接取每个分组的第一条记录（即排名第一的城市）：

from sqlalchemy import create_engine, MetaData, Table, Column, String, func
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import select, over

engine = create_engine('sqlite:///hotel.db', echo=True)
meta = MetaData()
hotel = Table(
    'hotel', meta,
    Column('country', String),
    Column('city', String),
    Column('name', String),
)
Session = sessionmaker(bind=engine)
session = Session()

# 子查询：给每个国家的城市按记录数排名
ranked_cities = select(
    hotel.c.country,
    hotel.c.city,
    func.count(hotel.c.name).label('city_count'),
    func.row_number().over(
        partition_by=hotel.c.country,
        order_by=func.count(hotel.c.name).desc()
    ).label('rank')
).group_by(hotel.c.country, hotel.c.city).subquery()

# 筛选出排名第一的城市
result = session.query(
    ranked_cities.c.country,
    ranked_cities.c.city,
    ranked_cities.c.city_count
).filter(ranked_cities.c.rank == 1).all()

print(result)

结果说明 #

两种方法都会返回符合预期的结果：

• India → Pune（3条记录）
• US → San Jose（2条记录）
• Brazil → abc（2条记录）

如果某个国家有多个城市记录数相同且都是最大值，方法一会返回所有符合条件的城市，方法二则只会返回其中一个（取决于数据库排序规则），你可以根据需求灵活选择。

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