基于DONE字段获取AGV当前及下一工序的SQL实现需求

我在生产工厂工作，车间内有多台自动导引车（AGV）运行，每台AGV携带唯一的车间控制码（SFC）对应特定生产物料。现有两张业务表：

• AR_PARAMETRIC_MEASURE：记录AGV的分配信息
• SFC_STEP：存储SFC的预设工序路由（仅在装配出错时会修改）

目前已通过SQL关联两张表，能获取指定SFC对应的最新AGV分配信息。现在需要新增两个字段：

• WORK_CENTER_NOW：AGV当前所在工序，规则为最后一个DONE = true的工序的下一个工序
• WORK_CENTER_NEXT：当前工序的下一个工序

注意：STEP_ID是类似'S1'、'S2'这类无法转成整数的字符串，不能用数值排序判断工序顺序，必须基于路由本身的顺序逻辑。期望最终结果表包含原有AGV分配信息+这两个新字段。

解决方案 #

以下方案基于通用SQL语法，需根据你使用的数据库（如MySQL、Oracle、SQL Server）微调函数细节。

前提假设 #

假设SFC_STEP表包含核心字段：

• SFC：与AR_PARAMETRIC_MEASURE的关联键
• STEP_ID：工序标识（如'S1'）
• WORK_CENTER：工序工位名称
• DONE：布尔值，标记工序是否完成

方案1：SFC_STEP有明确的工序顺序字段（如STEP_SEQ） #

如果表中已存储工序的顺序编号（如STEP_SEQ），可直接基于该字段计算：

WITH completed_steps AS (
    SELECT 
        s.SFC,
        MAX(s.STEP_SEQ) AS last_completed_seq
    FROM SFC_STEP s
    WHERE s.DONE = TRUE
    GROUP BY s.SFC
),
current_step AS (
    SELECT 
        s.SFC,
        s.WORK_CENTER AS WORK_CENTER_NOW,
        s.STEP_SEQ AS current_seq
    FROM SFC_STEP s
    JOIN completed_steps cs ON s.SFC = cs.SFC
    WHERE s.STEP_SEQ = cs.last_completed_seq + 1
),
next_step AS (
    SELECT 
        s.SFC,
        s.WORK_CENTER AS WORK_CENTER_NEXT
    FROM SFC_STEP s
    JOIN current_step cs ON s.SFC = cs.SFC
    WHERE s.STEP_SEQ = cs.current_seq + 1
)
SELECT 
    apm.*,
    cs.WORK_CENTER_NOW,
    ns.WORK_CENTER_NEXT
FROM AR_PARAMETRIC_MEASURE apm
LEFT JOIN current_step cs ON apm.SFC = cs.SFC
LEFT JOIN next_step ns ON apm.SFC = ns.SFC
-- 过滤指定SFC，可按需删除或修改
WHERE apm.SFC = 'TARGET_SFC';

方案2：SFC_STEP无明确顺序字段，基于STEP_ID字符串提取排序 #

若STEP_ID是前缀+数字格式（如'S1'、'S10'），可提取数字部分生成排序序列：

WITH step_order AS (
    SELECT 
        s.SFC,
        s.STEP_ID,
        s.WORK_CENTER,
        s.DONE,
        -- 提取STEP_ID中的数字部分转换为整数用于排序
        CAST(SUBSTRING(s.STEP_ID, 2) AS INT) AS step_num,
        ROW_NUMBER() OVER (PARTITION BY s.SFC ORDER BY CAST(SUBSTRING(s.STEP_ID, 2) AS INT)) AS step_seq
    FROM SFC_STEP s
),
completed_steps AS (
    SELECT 
        so.SFC,
        MAX(so.step_seq) AS last_completed_seq
    FROM step_order so
    WHERE so.DONE = TRUE
    GROUP BY so.SFC
),
current_step AS (
    SELECT 
        so.SFC,
        so.WORK_CENTER AS WORK_CENTER_NOW,
        so.step_seq AS current_seq
    FROM step_order so
    JOIN completed_steps cs ON so.SFC = cs.SFC
    WHERE so.step_seq = cs.last_completed_seq + 1
),
next_step AS (
    SELECT 
        so.SFC,
        so.WORK_CENTER AS WORK_CENTER_NEXT
    FROM step_order so
    JOIN current_step cs ON so.SFC = cs.SFC
    WHERE so.step_seq = cs.current_seq + 1
)
SELECT 
    apm.*,
    cs.WORK_CENTER_NOW,
    ns.WORK_CENTER_NEXT
FROM AR_PARAMETRIC_MEASURE apm
LEFT JOIN current_step cs ON apm.SFC = cs.SFC
LEFT JOIN next_step ns ON apm.SFC = ns.SFC
WHERE apm.SFC = 'TARGET_SFC';

方案3：依赖记录插入顺序排序 #

若STEP_ID无规律，可基于表的主键或创建时间生成工序顺序：

WITH step_order AS (
    SELECT 
        s.SFC,
        s.STEP_ID,
        s.WORK_CENTER,
        s.DONE,
        -- 按主键或创建时间排序生成工序序列
        ROW_NUMBER() OVER (PARTITION BY s.SFC ORDER BY s.ID) AS step_seq -- 替换为你的主键或CREATE_DATE字段
    FROM SFC_STEP s
),
completed_steps AS (
    SELECT 
        so.SFC,
        MAX(so.step_seq) AS last_completed_seq
    FROM step_order so
    WHERE so.DONE = TRUE
    GROUP BY so.SFC
),
current_step AS (
    SELECT 
        so.SFC,
        so.WORK_CENTER AS WORK_CENTER_NOW,
        so.step_seq AS current_seq
    FROM step_order so
    JOIN completed_steps cs ON so.SFC = cs.SFC
    WHERE so.step_seq = cs.last_completed_seq + 1
),
next_step AS (
    SELECT 
        so.SFC,
        so.WORK_CENTER AS WORK_CENTER_NEXT
    FROM step_order so
    JOIN current_step cs ON so.SFC = cs.SFC
    WHERE so.step_seq = cs.current_seq + 1
)
SELECT 
    apm.*,
    cs.WORK_CENTER_NOW,
    ns.WORK_CENTER_NEXT
FROM AR_PARAMETRIC_MEASURE apm
LEFT JOIN current_step cs ON apm.SFC = cs.SFC
LEFT JOIN next_step ns ON apm.SFC = ns.SFC
WHERE apm.SFC = 'TARGET_SFC';

补充说明 #

• 若所有工序已完成（last_completed_seq等于最大step_seq），WORK_CENTER_NOW会返回NULL，可根据业务需求修改为固定值（如'已完成'）
• 若当前工序是最后一道工序，WORK_CENTER_NEXT会返回NULL，同样可按需调整逻辑

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