如何用Flask-SQLAlchemy实现15个不同用户分数的升序查询

你的核心需求是从符合条件的分数记录中，每个用户仅保留最低分，再按分数升序取前15条。原代码的问题在于：distinct(Scores.user_id)配合order_by(Scores.user_id)只会保留每个用户的任意一条记录（而非最低分），后续的分数排序只是对这些随机记录重新排列，无法满足需求。以下是两种可行的解决方案：

方案一：窗口函数（推荐，精准控制取数逻辑） #

利用PostgreSQL的窗口函数row_number()，给每个用户的分数按「分数升序、记录ID升序」编号，取编号为1的记录（即该用户的最低分，若有多个相同最低分则取最早创建的那条），之后再整体排序取前15。

修改后的完整代码：

from sqlalchemy import func  # 需导入func模块

@blueprint.route("/")
def index():
    diff_arg = request.args.get("diff", 0)
    ver_arg = request.args.get("ver", GAME_VERSION)
    user_arg = request.args.get("user", None)

    # 先过滤版本和难度，缩小数据集提升查询效率
    scores = Scores.query
    if ver_arg:
        scores = scores.filter_by(version=ver_arg)
    scores = scores.filter_by(difficulty=diff_arg)

    if not user_arg:
        # 窗口函数：按用户分组，给每组内的分数按「分数升序、ID升序」编号
        row_num = func.row_number().over(
            partition_by=Scores.user_id,
            order_by=[Scores.score.asc(), Scores.id.asc()]
        )
        # 生成包含编号的子查询
        subquery = scores.add_columns(row_num.label('row_num')).subquery()
        # 关联子查询，仅保留每个用户编号为1的记录（最低分）
        scores = Scores.query.join(
            subquery,
            (Scores.id == subquery.c.id) & (subquery.c.row_num == 1)
        )
    else:
        if user := Users.query.filter_by(username=user_arg).first():
            scores = scores.filter_by(user_id=user.id)
        else:
            abort(404, "User not found")

    # 按分数升序排序，取前MAX_TOP_SCORES条（即15条）
    scores = scores.order_by(Scores.score.asc()).limit(MAX_TOP_SCORES).all()

    return render_template(
        "views/scores.html",
        scores=scores,
        diff=int(diff_arg),
        ver=ver_arg,
        user=user_arg
    )

方案二：分组聚合+关联（适合仅需核心字段的场景） #

先通过group_by和func.min()获取每个用户的最低分，再关联原表拿到完整记录。若用户存在多个相同最低分，需额外添加去重逻辑：

from sqlalchemy import func

@blueprint.route("/")
def index():
    diff_arg = request.args.get("diff", 0)
    ver_arg = request.args.get("ver", GAME_VERSION)
    user_arg = request.args.get("user", None)

    scores = Scores.query
    if ver_arg:
        scores = scores.filter_by(version=ver_arg)
    scores = scores.filter_by(difficulty=diff_arg)

    if not user_arg:
        # 子查询：按用户分组，获取每个用户的最低分
        subquery = scores.with_entities(
            Scores.user_id,
            func.min(Scores.score).label('min_score')
        ).group_by(Scores.user_id).subquery()
        # 关联原表，拿到对应用户最低分的记录
        scores = Scores.query.join(
            subquery,
            (Scores.user_id == subquery.c.user_id) & (Scores.score == subquery.c.min_score)
        ).distinct(Scores.user_id)  # 处理用户有多个相同最低分的情况
    else:
        if user := Users.query.filter_by(username=user_arg).first():
            scores = scores.filter_by(user_id=user.id)
        else:
            abort(404, "User not found")

    scores = scores.order_by(Scores.score.asc()).limit(MAX_TOP_SCORES).all()

    return render_template(
        "views/scores.html",
        scores=scores,
        diff=int(diff_arg),
        ver=ver_arg,
        user=user_arg
    )

原代码失效原因 #

PostgreSQL的distinct(col)行为完全依赖前置的order_by：它会按照order_by的顺序保留每组的第一条记录。你之前先按user_id排序再去重，拿到的是每个用户的任意一条记录（而非最低分），后续的分数排序只是对这些随机记录重新排列，自然不符合需求。

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