多条件SQL排序实现：不同员工类型分规则排序的优雅查询方案

给定如下People表：

Name  | Id  | Employee type
-------------------------------
  Max   | 2   | Engineer
  Alice | 3   | Scientist
  Bob   | 1   | Scientist
  John  | 4   | Engineer
  Jane  | 5   | Engineer

需要实现的排序规则：

• 优先展示Scientist，再展示Engineer
• Scientist按name升序排列
• Engineer按id降序排列

不用拆分查询再合并的方式，直接用CASE表达式构建排序逻辑即可实现优雅的单查询方案，具体代码如下：

SQLAlchemy 查询实现 #

假设已定义People模型：

from sqlalchemy import Column, Integer, String, case
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class People(Base):
    __tablename__ = 'people'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    employee_type = Column(String)

核心查询代码：

from sqlalchemy.orm import sessionmaker

# 假设已初始化engine并创建session
session = sessionmaker(bind=engine)()

sorted_results = session.query(People).order_by(
    # 第一步：按员工类型优先级排序，Scientist在前
    case(
        (People.employee_type == 'Scientist', 1),
        (People.employee_type == 'Engineer', 2),
        else_=3  # 其他类型默认排最后
    ),
    # 第二步：同类型内按对应规则排序
    case(
        (People.employee_type == 'Scientist', People.name),
        else_=People.id.desc()
    )
).all()

逻辑说明：

• 第一个case给不同员工类型分配优先级数值，数值越小排序越靠前
• 第二个case根据员工类型选择排序字段：Scientist用name升序（SQLAlchemy默认升序），Engineer用id降序

普通SQL语句实现 #

写法一（用负号实现降序） #

SELECT *
FROM people
ORDER BY
    -- 员工类型优先级排序
    CASE employee_type
        WHEN 'Scientist' THEN 1
        WHEN 'Engineer' THEN 2
        ELSE 3
    END,
    -- 同类型内排序
    CASE employee_type
        WHEN 'Scientist' THEN name
        ELSE -id  -- 对id取负，升序排序等价于原id降序
    END ASC;

写法二（直接指定排序方向，可读性更强） #

SELECT *
FROM people
ORDER BY
    -- 员工类型优先级排序
    CASE employee_type
        WHEN 'Scientist' THEN 1
        WHEN 'Engineer' THEN 2
        ELSE 3
    END,
    -- Scientist按name升序
    CASE WHEN employee_type = 'Scientist' THEN name END ASC,
    -- Engineer按id降序
    CASE WHEN employee_type = 'Engineer' THEN id END DESC;

第二种写法更直观，直接针对不同类型明确指定排序字段和方向，维护起来更方便。

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