如何在SQLAlchemy中限制子查询结果？获取每个问题的最高猜测值

原代码的核心问题 #

1. 分组逻辑错误：原查询中group_by(Guess.id, Guess.amount)完全无法实现“按问题分组取最高金额”的需求——因为Guess.id是主键，每个Guess记录的id唯一，这样分组后每条记录单独成组，max(Guess.amount)就是该记录自身的金额，和直接查所有Guess记录没有区别。
2. LIMIT位置错误：添加.limit(1)后，只会从全局所有Guess记录中取金额最高的那一条，而不是每个问题下的最高记录。当执行outerjoin时，只有对应这条记录的Question能匹配到数据，其他Question自然返回None。

正确解决方案 #

方案一：先分组取最高金额，再关联回Guess表

先通过子查询获取每个问题对应的最高金额，再关联Guess表找到对应金额的记录（若存在多个同金额的最高猜测，可通过额外条件确保唯一）：

# 第一步：子查询获取每个问题的最高金额
subquery_max = db.session.query(
    Guess.question_id,
    db.func.max(Guess.amount).label("highest_amount")
).group_by(Guess.question_id).subquery()

# 第二步：关联Question、最高金额子查询、Guess表，获取目标数据
query = db.session.query(
    Question,
    Guess.id.label("guess_id"),
    subquery_max.c.highest_amount
).outerjoin(
    subquery_max, Question.id == subquery_max.c.question_id
).outerjoin(
    Guess, 
    db.and_(
        Guess.question_id == Question.id,
        Guess.amount == subquery_max.c.highest_amount
    )
).group_by(Question.id, subquery_max.c.highest_amount, Guess.id).all()

如果存在多个同金额的最高猜测，想要每个问题只返回一条结果（比如取id最大的猜测），可以调整查询：

query = db.session.query(
    Question,
    db.func.max(Guess.id).label("guess_id"),
    subquery_max.c.highest_amount
).outerjoin(
    subquery_max, Question.id == subquery_max.c.question_id
).outerjoin(
    Guess, 
    db.and_(
        Guess.question_id == Question.id,
        Guess.amount == subquery_max.c.highest_amount
    )
).group_by(Question.id, subquery_max.c.highest_amount).all()

方案二：使用窗口函数（更灵活）

利用row_number()窗口函数，给每个问题下的猜测按金额降序编号，取编号为1的记录（自动处理多最高金额的情况）：

from sqlalchemy import func, over

# 子查询给每个问题下的猜测按金额降序排号
subquery_rn = db.session.query(
    Guess,
    func.row_number().over(
        partition_by=Guess.question_id,  # 按问题分组
        order_by=Guess.amount.desc(), Guess.id.desc()  # 金额降序，id降序确保唯一
    ).label("rn")
).subquery()

# 关联Question表，取每个问题的第一条（最高金额）猜测
query = db.session.query(
    Question,
    subquery_rn.c.id.label("guess_id"),
    subquery_rn.c.amount.label("highest_amount")
).outerjoin(
    subquery_rn, 
    db.and_(
        Question.id == subquery_rn.c.question_id,
        subquery_rn.c.rn == 1
    )
).all()

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