怎样在SQL中通过JOIN操作还原多级审批流历史轨迹-SQL-PHP中文网

直接用left join连多张审批表会丢记录，因为中间某级无匹配行时后续left join基于null关联导致整行被过滤；应以流程头表为左基统一关联，或用union all拍平再排序。

怎样在sql中通过join操作还原多级审批流历史轨迹

为什么直接用LEFT JOIN连多张审批表会丢记录

多级审批流通常有「申请人→部门主管→总监→VP」这样的层级，每级审批人、时间、状态都存在独立记录表里。很多人第一反应是写一串 LEFT JOIN 把 approval_step1、approval_step2、approval_step3 全连上，结果发现：只要某条流程只走到第二级，第三级字段全为 NULL，但更严重的是——有些流程压根没出现在结果里。

根本原因是：用 LEFT JOIN 时，如果中间某一级表没有匹配行（比如 step2 表里缺数据），后续的 LEFT JOIN step3 就是在 NULL 基础上再左连，逻辑成立但实际查不到原始主键关联，最终导致整行被过滤掉（尤其当 WHERE 条件里误用了 step3 的非空字段）。

确保主表是流程头表（如 workflow_header），所有 JOIN 都以它为左基
每级审批表必须用 ON header.id = stepN.workflow_id 关联，不能跨级关联（比如用 step1.id 关联 step2）
所有 WHERE 条件中涉及审批表字段的，都要允许 IS NULL，例如 step2.status IS NULL OR step2.status != 'rejected'

用UNION ALL把各层级“拍平”再按序排列

比嵌套 JOIN 更可靠的做法，是把每一级审批当作同构事件：都有流程ID、审批人、时间、状态、顺序号。用 UNION ALL 合并，再统一排序，能天然兼容任意深度（1级到5级都适用），也不会因某级缺失而丢整个流程。

SELECT workflow_id, 'step1' AS level, approver, approved_at, status, 1 AS sort_order
FROM approval_step1
UNION ALL
SELECT workflow_id, 'step2', approver, approved_at, status, 2
FROM approval_step2
UNION ALL
SELECT workflow_id, 'step3', approver, approved_at, status, 3
FROM approval_step3
ORDER BY workflow_id, sort_order;

必须用 UNION ALL，不是 UNION（避免隐式去重，审批人重名或时间相同会导致记录合并）
每个子查询里显式写死 sort_order，别依赖表名顺序——数据库不保证 UNION ALL 的执行顺序
如果某级审批可能有多条记录（比如重审），需要在子查询里加 LIMIT 1 或用窗口函数取最新一条，否则会重复拉出轨迹

用窗口函数给每个流程内审批步骤编号

光靠 sort_order 字段只能表达预设层级，无法反映真实审批顺序。例如「总监先批、主管后补签」这种逆序操作，或者同一级多人并行审批（需全部完成才进下一级）。这时得用 ROW_NUMBER() 按时间动态排序：

SELECT 
  workflow_id,
  approver,
  approved_at,
  status,
  ROW_NUMBER() OVER (PARTITION BY workflow_id ORDER BY approved_at) AS actual_seq
FROM (
  SELECT workflow_id, approver, approved_at, status FROM approval_step1
  UNION ALL
  SELECT workflow_id, approver, approved_at, status FROM approval_step2
  UNION ALL
  SELECT workflow_id, approver, approved_at, status FROM approval_step3
) t;

PARTITION BY workflow_id 是关键，确保编号只在单个流程内连续
ORDER BY approved_at 要确认时间字段是否带时区、是否已清洗（比如 '0001-01-01' 这种占位时间会排最前，得先 WHERE approved_at > '1970-01-01' 过滤）
如果审批时间精度是秒级且存在并发提交，建议加上 id 作为二级排序：ORDER BY approved_at, id，避免窗口函数随机取值

还原完整轨迹时最容易忽略的空状态处理

真实系统里，审批表往往只存「已操作」记录，未到达的环节在数据库里完全不存在。但业务方要的轨迹是「从发起→待部门主管→待总监→已完成」这样的全路径。这时候不能只查有数据的行。

在应用层拼接更可控：先查出流程头和所有已审批记录，再根据预设审批规则（如 JSON 配置的 ["dept_head", "director", "vp"]）补全中间空状态

如果坚持 SQL 内解决，可用 VALUES 构造层级序列，再 LEFT JOIN 审批记录，例如：

SELECT v.level, v.role, s.approver, s.status, s.approved_at
FROM (VALUES (1,'dept_head'),(2,'director'),(3,'vp')) AS v(level,role)
LEFT JOIN approval_records s ON v.role = s.role AND s.workflow_id = ?
ORDER BY v.level;