SQLAlchemy执行union合并两个分组查询报错如何解决

核心错误点 #

• 直接调用.all()执行了查询：qry1和qry2末尾加了.all()，执行后返回的是数据结果列表，不是SQLAlchemy的Query构造对象，而union()要求传入未执行的查询语句对象，这是触发报错的直接原因。
• 列逻辑不匹配原始SQL：原始SQL中第一个查询的total_price、第二个查询的TotalHour/overtime_TotalHour都是NULL值占位，你当前代码错误引用了未关联表的字段，既会触发字段不存在的报错，也不符合业务逻辑。
• 过滤条件运算符错误：filter中不能用Python原生的and连接条件，要使用SQLAlchemy提供的and_，或者直接把多个条件用逗号分隔传入filter。
• 多余的分组字段：第一个查询不需要把total_pric加入分组，第二个也不需要把工时字段加入分组，和原始SQL逻辑不符。
• 缺失表关联：你写的查询没有声明表的JOIN关系，仅靠filter里的关联条件，写法不规范且容易出现笛卡尔集问题。

修正后的实现代码 #

from sqlalchemy import func, cast, Numeric, union, null, coalesce
from sqlalchemy.orm import Session

def get_allusers_monthly(db: Session):
    # 第一个查询：统计工时，支出字段设为NULL占位
    qry1 = db.query(
        func.to_char(workhour.Workhour.date, 'YYYY-MM').label('year_month'),
        department.Department.department_name.label('department_name'),
        user.User.username.label('user_name'),
        func.sum(workhour.Workhour.hour).label('total_hour'),
        func.sum(workhour.Workhour.overtime_hour).label('total_overtime_hour'),
        cast(null(), Numeric(20)).label('total_price')
    ).select_from(user.User)\
     .join(workhour.Workhour, user.User.id == workhour.Workhour.user_id)\
     .join(department.Department, user.User.department_id == department.Department.id)\
     .group_by('year_month', 'department_name', 'user_name')

    # 第二个查询：统计支出，工时字段设为NULL占位
    qry2 = db.query(
        func.to_char(expen.Expenditure.date, 'YYYY-MM').label('year_month'),
        department.Department.department_name.label('department_name'),
        user.User.username.label('user_name'),
        cast(null(), Numeric(20)).label('total_hour'),
        cast(null(), Numeric(20)).label('total_overtime_hour'),
        func.sum(expen.Expenditure.price).label('total_price')
    ).select_from(user.User)\
     .join(expen.Expenditure, user.User.id == expen.Expenditure.user_id)\
     .join(department.Department, user.User.department_id == department.Department.id)\
     .group_by('year_month', 'department_name', 'user_name')

    # 合并两个查询后做二次聚合，将同一用户同一月的两条数据合并为一条
    union_sub = qry1.union_all(qry2).subquery()
    final_qry = db.query(
        union_sub.c.year_month,
        union_sub.c.department_name,
        union_sub.c.user_name,
        coalesce(func.sum(union_sub.c.total_hour), 0).label('total_hour'),
        coalesce(func.sum(union_sub.c.total_overtime_hour), 0).label('total_overtime_hour'),
        coalesce(func.sum(union_sub.c.total_price), 0).label('total_price')
    ).group_by(
        union_sub.c.year_month,
        union_sub.c.department_name,
        union_sub.c.user_name
    ).order_by(union_sub.c.year_month.desc())

    # 需返回结果集加.all()，需返回查询对象直接返回final_qry即可
    return final_qry.all()

优化说明 #

• 用union_all替代union：两个子查询的结果不存在重复行，union_all不需要做去重逻辑，性能远高于union。
• 增加coalesce函数把NULL值转为0，避免统计结果出现空值影响后续使用。
• 显式声明JOIN关系，代码可读性更高，也避免隐式连接导致的逻辑错误。
• 额外增加了一次聚合，把同一个用户同一个月的工时行、支出行合并为一行，匹配最终分组求和的需求。

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