SQLAlchemy ORM按VARCHAR字段GROUP BY出现额外分组问题求助

问题描述 #

使用Python的SQLAlchemy操作MySQL数据库时遇到如下问题：

• 在Navicat等SQL管理工具中执行原生SQL，按q1.member_login（VARCHAR类型）分组后得到49k条无重复记录，结果正常；
• 转换为SQLAlchemy ORM代码后，WHERE cr.val = 1之前的逻辑正常，但按q1.member_login分组时出现重复记录；
• 测试发现按id分组结果与原生SQL一致，但按VARCHAR类型字段分组时，会额外按未指定的字段进行分组，导致member_login值重复。

原生SQL语句 #

select q1.*, cr.psubject_code, fs.num_of_credit as NOC, sum(cr.grade * fs.num_of_credit) as total_grade, sum(fs.num_of_credit) as total_NOC, sum(cr.grade * fs.num_of_credit)/sum(fs.num_of_credit) as average_grade
from 
(select fg.id as group_id, fg.pterm_id as term_id, fg.pterm_name as term_name, fg.psubject_name as subject_name,
fgm.member_login
from fu_group fg
join fu_group_member fgm on fg.id = fgm.groupid
where fg.pterm_id >= 24 and fg.is_virtual = 0) q1 JOIN t7_course_result cr on (cr.groupid = q1.group_id and q1.member_login = cr.student_login) join fu_subject fs on cr.psubject_code = fs.subject_code
where cr.val = 1
group by q1.member_login

SQLAlchemy ORM代码 #

async def avg_grade(engine, partitioner, mod_number):
    q1 = select(
        fu_group.id,
        fu_group.pterm_id,
        fu_group_member.member_login,
        fu_group.pterm_name
    ).join_from(
        fu_group_member,
        fu_group,
        fu_group.id == fu_group_member.groupid
    ).where(
        fu_group.pterm_id >= 24,
        fu_group.is_virtual == 0,
    ).subquery()
    q = select(
        q1.c.id,
        q1.c.pterm_id,
        q1.c.member_login,
        q1.c.pterm_name,
        t7_course_result.psubject_code,
        func.sum(t7_course_result.grade * fu_subject.num_of_credit),
        func.sum(fu_subject.num_of_credit),
        cast(func.sum(t7_course_result.grade * fu_subject.num_of_credit)/func.sum(fu_subject.num_of_credit), DECIMAL(10, 1))
    ).join_from(
        q1,
        t7_course_result,
        and_(
            q1.c.id == t7_course_result.groupid,
            q1.c.member_login == t7_course_result.student_login)
    ).join_from(
        t7_course_result,
        fu_subject,
        t7_course_result.psubject_code == fu_subject.subject_code
    ).where(
        t7_course_result.val == 1,
        q1.c.id % 17 == mod_number
    ).group_by(
        q1.c.member_login)

ORM模型类定义 #

class fu_group(Base):
    __tablename__= 'fu_group'
    id = Column(BigInteger, primary_key=True)
    pterm_id = Column(Integer, index=True)
    pterm_name = Column(VARCHAR, index=True)
    is_virtual = Column(SmallInteger, index=True)
    psubject_name = Column(VARCHAR, index=True)

class fu_group_member(Base):
    __tablename__ = 'fu_group_member'
    id = Column(BigInteger, primary_key=True)
    groupid = Column(BigInteger, index=True)
    member_login = Column(VARCHAR, index=True)

class t7_course_result(Base):
    __tablename__ = 't7_course_result'
    id = Column(BigInteger, primary_key=True)
    groupid = Column(BigInteger, index=True)
    student_login = Column(VARCHAR, index=True)
    val = Column(VARCHAR, index=True)
    grade = Column(DECIMAL(10, 1), index=True)
    psubject_code = Column(VARCHAR(25), index=True)

class fu_subject(Base):
    __tablename__ = 'fu_subject'
    id = Column(BigInteger, primary_key=True)
    department_id = Column(Integer, index=True)
    subject_code = Column(VARCHAR(25), index=True)
    num_of_credit = Column(Integer, index=True)

原因分析 #

这不是SQLAlchemy的bug，核心是MySQL的SQL模式与SQLAlchemy分组逻辑的差异：

1. MySQL的ONLY_FULL_GROUP_BY模式：你的原生SQL中SELECT q1.*包含了id、pterm_id等非聚合字段，但仅按member_login分组却能正常执行，说明你的MySQL未开启ONLY_FULL_GROUP_BY模式。此时MySQL会隐式选择这些非分组字段的任意值，最终呈现出member_login无重复的结果。
2. SQLAlchemy的分组行为：当ORM的SELECT语句中包含非聚合字段（如q1.c.id、q1.c.pterm_id）但仅指定member_login作为分组字段时，SQLAlchemy会遵循SQL标准（或适配MySQL的模式），自动将这些非聚合字段加入GROUP BY子句，导致分组维度变多——同一个member_login可能对应不同的id或pterm_id，因此分组后会产生多条重复的member_login记录。

解决方案 #

方案1：移除不需要的非聚合字段 #

如果业务上不需要id、pterm_id等非聚合字段，直接从SELECT中删除，只保留分组字段和聚合结果：

q = select(
    q1.c.member_login,
    func.sum(t7_course_result.grade * fu_subject.num_of_credit),
    func.sum(fu_subject.num_of_credit),
    cast(func.sum(t7_course_result.grade * fu_subject.num_of_credit)/func.sum(fu_subject.num_of_credit), DECIMAL(10, 1))
).join_from(
    q1,
    t7_course_result,
    and_(
        q1.c.id == t7_course_result.groupid,
        q1.c.member_login == t7_course_result.student_login)
).join_from(
    t7_course_result,
    fu_subject,
    t7_course_result.psubject_code == fu_subject.subject_code
).where(
    t7_course_result.val == 1,
    q1.c.id % 17 == mod_number
).group_by(
    q1.c.member_login)

方案2：明确所有分组字段（若需保留非聚合字段） #

如果必须保留id、pterm_id等字段，需将它们也加入GROUP BY（但这会改变分组逻辑，与原生SQL的“随机取非分组字段值”行为不一致）：

.group_by(
    q1.c.member_login, q1.c.id, q1.c.pterm_id, q1.c.pterm_name, t7_course_result.psubject_code
)

方案3：关闭MySQL的ONLY_FULL_GROUP_BY模式（不推荐） #

若要完全对齐原生SQL的非标准行为，可以修改MySQL的sql_mode，移除ONLY_FULL_GROUP_BY。但此方法不符合SQL标准，可能导致结果不可预期，不建议在生产环境使用。

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