如何在SQL/SQLAlchemy中结合多表记录使用JOIN与ORDER_BY？

我来给你详细拆解怎么实现这两张表的关联查询并排序，不管是用原生SQL还是SQLAlchemy，都很容易上手，咱们分场景来看：

一、原生SQL实现 #

首先是最直接的原生SQL写法，核心是用JOIN关联两张表，再用ORDER BY指定排序规则。

基础关联+排序示例 #

假设我们要获取所有有成绩记录的学生信息，同时带上他们的课程成绩，并且按专业升序、成绩降序来排序，SQL语句如下：

SELECT 
    si.student_id, 
    si.major, 
    sg.course, 
    sg.semester, 
    sg.grade
FROM student_info si
INNER JOIN student_grade sg 
    ON si.student_id = sg.student_id
ORDER BY 
    si.major ASC, 
    sg.grade DESC;

关键细节说明 #

• 关联类型：这里用的INNER JOIN只会返回两边表都匹配的记录（也就是有成绩的学生）。如果想包含所有学生，哪怕没修课没成绩，就换成LEFT JOIN，这样没成绩的学生对应的课程、学期、成绩字段会显示NULL。
• 排序规则：ORDER BY后面可以跟多个字段，用逗号分隔。ASC是升序（默认可以省略），DESC是降序。比如上面的例子里，先把同专业的学生归到一起，再把同专业里成绩高的排在前面。

二、SQLAlchemy实现 #

SQLAlchemy有两种常用的使用方式：Core（核心层，偏SQL原生）和ORM（对象关系映射，更面向对象），咱们分别来看：

2.1 SQLAlchemy Core方式 #

先定义表结构，再构建查询：

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Float
from sqlalchemy import select

# 初始化数据库连接和元数据
engine = create_engine('your_database_url')  # 替换成你的数据库链接，比如mysql+pymysql://user:pass@host/db
metadata = MetaData()

# 定义两张表的结构
student_info = Table(
    'student_info', metadata,
    Column('student_id', Integer, primary_key=True),
    Column('major', String)
)

student_grade = Table(
    'student_grade', metadata,
    Column('student_id', Integer, primary_key=True),
    Column('course', String),
    Column('semester', String),
    Column('grade', Float)
)

# 构建关联查询并排序
query = select(
    student_info.c.student_id,
    student_info.c.major,
    student_grade.c.course,
    student_grade.c.semester,
    student_grade.c.grade
).select_from(
    # 默认是INNER JOIN，要LEFT JOIN的话用outerjoin
    student_info.join(student_grade, student_info.c.student_id == student_grade.c.student_id)
).order_by(
    student_info.c.major.asc(),  # 专业升序
    student_grade.c.grade.desc()  # 成绩降序
)

# 执行查询并输出结果
with engine.connect() as conn:
    result = conn.execute(query)
    for row in result:
        print(f"学生ID: {row.student_id}, 专业: {row.major}, 课程: {row.course}, 学期: {row.semester}, 成绩: {row.grade}")

2.2 SQLAlchemy ORM方式 #

ORM方式更偏向于面向对象，先定义模型类，再通过会话查询：

from sqlalchemy import Column, Integer, String, Float, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

# 初始化基础模型
Base = declarative_base()

# 定义学生信息模型
class StudentInfo(Base):
    __tablename__ = 'student_info'
    student_id = Column(Integer, primary_key=True)
    major = Column(String)
    # 关联成绩表，反向关联是StudentGrade的student字段
    grades = relationship('StudentGrade', back_populates='student')

# 定义成绩模型
class StudentGrade(Base):
    __tablename__ = 'student_grade'
    # 用学生ID+课程作为联合主键（因为一个学生可能多门课）
    student_id = Column(Integer, ForeignKey('student_info.student_id'), primary_key=True)
    course = Column(String, primary_key=True)
    semester = Column(String)
    grade = Column(Float)
    # 关联学生信息表
    student = relationship('StudentInfo', back_populates='grades')

# 创建数据库会话
engine = create_engine('your_database_url')
Session = sessionmaker(bind=engine)
session = Session()

# 执行关联查询并排序
results = session.query(
    StudentInfo.student_id,
    StudentInfo.major,
    StudentGrade.course,
    StudentGrade.semester,
    StudentGrade.grade
).join(StudentGrade).order_by(
    StudentInfo.major.asc(),
    StudentGrade.grade.desc()
).all()

# 遍历输出结果
for row in results:
    print(f"学生ID: {row.student_id}, 专业: {row.major}, 课程: {row.course}, 学期: {row.semester}, 成绩: {row.grade}")

ORM的额外说明 #

• 如果想获取完整的模型对象而不是单独字段，可以把查询改成session.query(StudentInfo, StudentGrade).join(StudentGrade)...，这样返回的是(StudentInfo实例, StudentGrade实例)的元组。
• 同样，要使用LEFT JOIN的话，把join(StudentGrade)换成outerjoin(StudentGrade)即可。

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