SQL ORM关联查询添加字段后结果重复，求修正方案

问题根源 #

原查询返回494条BusinessRole记录，关联Approval后变为959条，核心原因是一个BusinessRole对应多条Approval记录，JOIN操作会将每个关联的Approval展开，导致主表数据重复。要保持494条结果，必须确保每个BusinessRole仅返回一次，同时基于Approval.approver字段排序。

具体实现方案 #

方案1：子查询获取目标排序字段 #

先通过子查询为每个BusinessRole匹配唯一的Approval记录（比如最新创建的），提取approver作为排序依据，再关联主表：

# 子查询：为每个BusinessRole获取指定的Approval记录的approver
approval_subquery = (
    db.session.query(
        Approval.business_role_id,
        Approval.approver
    )
    # 按创建时间倒序，确保取最新的Approval记录
    .order_by(Approval.created_at.desc())
    # 按business_role_id去重，每个角色只保留一条Approval
    .distinct(Approval.business_role_id)
    .subquery()
)

# 主查询：关联子查询，按approver排序，确保每个BusinessRole仅返回一次
final_query = (
    db.session.query(BusinessRole)
    .outerjoin(approval_subquery, BusinessRole.id == approval_subquery.c.business_role_id)
    # 处理approver为空的情况，用coalesce统一排序规则
    .order_by(func.coalesce(approval_subquery.c.approver, ''))
)

result = final_query.all()

方案2：窗口函数标记主记录 #

用窗口函数为每个BusinessRole的Approval记录编号，仅取序号为1的记录，再关联主表排序：

# 子查询：用窗口函数为每个BusinessRole的Approval记录标记序号
approval_window_subquery = (
    db.session.query(
        Approval.business_role_id,
        Approval.approver,
        func.row_number().over(
            partition_by=Approval.business_role_id,
            # 可根据业务需求调整排序规则，比如按审批状态、时间等
            order_by=Approval.created_at.desc()
        ).label('record_rank')
    )
    .subquery()
)

# 主查询：仅关联序号为1的Approval记录，确保主表无重复
final_query = (
    db.session.query(BusinessRole)
    .outerjoin(
        approval_window_subquery,
        (BusinessRole.id == approval_window_subquery.c.business_role_id) 
        & (approval_window_subquery.c.record_rank == 1)
    )
    .order_by(approval_window_subquery.c.approver)
)

result = final_query.all()

方案3：GROUP BY去重（需注意数据库兼容性） #

通过GROUP BY主表主键确保每个BusinessRole仅返回一次，同时用聚合函数指定排序用的approver：

final_query = (
    db.session.query(BusinessRole)
    .join(Approval, BusinessRole.id == Approval.business_role_id)
    .group_by(BusinessRole.id)
    # 用max/min或其他聚合函数取目标approver，需根据业务逻辑选择
    .order_by(func.max(Approval.approver))
)

result = final_query.all()

注：该方案依赖数据库对非聚合字段的GROUP BY支持，比如MySQL需关闭ONLY_FULL_GROUP_BY，PostgreSQL需用DISTINCT ON替代更稳妥。

关键注意事项 #

• 必须明确一个BusinessRole对应多条Approval时，选取哪一条的approver用于排序（如最新审批、状态为通过的记录等），否则排序逻辑会失去意义。
• 若需兼容无Approval关联的BusinessRole，请使用outerjoin而非join，避免丢失主表数据。

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