Postgres采购审批场景：如何优化多表查询查看他人审批状态

需求说明 #

咱们的内部采购审批系统里：

• users表存审批员工信息
• approvals表存采购订单数据
• user_approvals表记录订单对应的审批人及审批状态

现在要实现：让某个审批人（比如user_id=3）既能看到自己需要审批的所有订单，又能查看这些订单上其他审批人的审批状态，同时优化原有的冗余嵌套查询。

优化后的查询方案 #

方案1：JOIN关联查询（推荐） #

直接通过关联查询定位当前用户参与的订单，再拉取这些订单的所有审批记录，一次查询就能拿到订单详情和全部审批人状态：

SELECT 
    a.id AS approval_id,
    -- 建议按需指定字段，不要用*，避免冗余数据
    a.order_no, a.amount, a.department_id,
    ua.user_id,
    u.name AS approver_name,
    ua.status AS approval_status
FROM approvals a
-- 第一步：筛选当前用户参与的订单
JOIN user_approvals ua_current ON a.id = ua_current.approval_id 
    AND ua_current.user_id = 3
-- 第二步：关联该订单的所有审批记录
JOIN user_approvals ua ON a.id = ua.approval_id
-- 关联用户表获取审批人名称（不需要的话可以去掉这行）
JOIN users u ON ua.user_id = u.id
ORDER BY a.id, ua.user_id;

方案2：CTE分步查询 #

如果偏好拆分逻辑，用公共表表达式（CTE）先圈出当前用户参与的订单ID，再关联审批记录，可读性更强：

WITH my_approval_orders AS (
    SELECT approval_id 
    FROM user_approvals 
    WHERE user_id = 3
)
SELECT 
    a.id AS approval_id,
    ua.user_id,
    u.name AS approver_name,
    ua.status
FROM approvals a
JOIN my_approval_orders mo ON a.id = mo.approval_id
JOIN user_approvals ua ON a.id = ua.approval_id
JOIN users u ON ua.user_id = u.id;

方案优势 #

1. 性能更优：JOIN操作比多层嵌套IN的查询效率更高，PostgreSQL对JOIN的优化更成熟
2. 逻辑清晰：避免嵌套层级，后续维护更方便
3. 信息完整：一次查询就能同时获取订单详情和所有审批人的状态，不用多次查询

原查询的问题 #

原嵌套IN查询不仅冗余，而且只能拿到状态字段，无法关联订单信息和审批人详情，实用性有限。优化后的方案能一次性满足业务需求，同时保证查询效率。

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