基于ROW_NUMBER的递归/循环查询求助：物料追溯与EndedAt构建

我明白你现在的困扰：嵌套查询只能拿到部分关联行，没法完整回溯从指定EndCode到所有上游StartCode的路径，还需要给每一行加上后续事件的EndedAt字段。其实递归CTE就是解决这类层级遍历问题的最佳方案，我来给你梳理清楚具体实现：

核心思路 #

物料流转是多分支的层级结构（比如A/B→C→F→G→J→L→M，同时D/E也指向F），嵌套查询只能处理单层关联，而递归CTE可以遍历整个上下游路径，同时把下游步骤的StartedAt赋值给上游步骤的EndedAt，完美匹配你的需求。

完整SQL实现 #

下面的代码会从指定EndCode（比如M或U）回溯所有关联的流转步骤，并生成EndedAt字段：

WITH RecursiveMaterialFlow AS (
    -- 锚点：从目标EndCode对应的最下游步骤开始
    SELECT 
        mt.StartCode,
        mt.StartedAt,
        mt.EndCode,
        CAST(NULL AS DATETIME) AS EndedAt, -- 最下游步骤无后续，EndedAt设为NULL
        1 AS HierarchyLevel
    FROM MyTable mt
    WHERE mt.EndCode = 'M' -- 替换为你需要查询的目标EndCode，比如'U'
    
    UNION ALL
    
    -- 递归：向上游查找所有以当前步骤StartCode为EndCode的流转记录
    SELECT 
        parent.StartCode,
        parent.StartedAt,
        parent.EndCode,
        child.StartedAt AS EndedAt, -- 后续步骤的StartedAt就是当前步骤的EndedAt
        child.HierarchyLevel + 1 AS HierarchyLevel
    FROM MyTable parent
    INNER JOIN RecursiveMaterialFlow child 
        ON parent.EndCode = child.StartCode
)
-- 最终结果：按层级从上游到下游排序，方便查看流转路径
SELECT 
    StartCode,
    StartedAt,
    EndCode,
    EndedAt
FROM RecursiveMaterialFlow
ORDER BY HierarchyLevel DESC, StartedAt;

代码解释 #

1. 锚点成员：先定位到目标EndCode对应的最下游流转步骤（比如EndCode='M'对应的StartCode=L），这一步没有后续，所以EndedAt设为NULL。
2. 递归成员：不断向上游查找，把当前步骤的StartCode作为上游步骤的EndCode，同时将当前步骤的StartedAt赋值给上游步骤的EndedAt（也就是上游步骤的后续事件时间）。
3. 层级标记：HierarchyLevel字段帮你区分节点在流转路径中的位置，数值越大代表越上游的步骤。

扩展：创建可复用的报表视图/函数 #

如果需要频繁按不同EndCode查询，可以封装成可复用的对象：

以MySQL为例创建视图（需结合会话参数） #

-- 设置目标EndCode参数
SET @TargetEndCode = 'M';

-- 创建视图
CREATE VIEW MaterialFlowReport AS
WITH RecursiveMaterialFlow AS (
    SELECT 
        mt.StartCode,
        mt.StartedAt,
        mt.EndCode,
        NULL AS EndedAt,
        1 AS HierarchyLevel
    FROM MyTable mt
    WHERE mt.EndCode = @TargetEndCode
    
    UNION ALL
    
    SELECT 
        parent.StartCode,
        parent.StartedAt,
        parent.EndCode,
        child.StartedAt AS EndedAt,
        child.HierarchyLevel + 1 AS HierarchyLevel
    FROM MyTable parent
    INNER JOIN RecursiveMaterialFlow child 
        ON parent.EndCode = child.StartCode
)
SELECT 
    StartCode,
    StartedAt,
    EndCode,
    EndedAt
FROM RecursiveMaterialFlow
ORDER BY HierarchyLevel DESC, StartedAt;

以PostgreSQL为例创建函数 #

CREATE OR REPLACE FUNCTION GetMaterialFlowReport(target_endcode VARCHAR(1))
RETURNS TABLE (
    StartCode VARCHAR(1),
    StartedAt TIMESTAMP,
    EndCode VARCHAR(1),
    EndedAt TIMESTAMP
) AS $$
WITH RecursiveMaterialFlow AS (
    SELECT 
        mt.StartCode,
        mt.StartedAt,
        mt.EndCode,
        NULL::TIMESTAMP AS EndedAt,
        1 AS HierarchyLevel
    FROM MyTable mt
    WHERE mt.EndCode = target_endcode
    
    UNION ALL
    
    SELECT 
        parent.StartCode,
        parent.StartedAt,
        parent.EndCode,
        child.StartedAt AS EndedAt,
        child.HierarchyLevel + 1 AS HierarchyLevel
    FROM MyTable parent
    INNER JOIN RecursiveMaterialFlow child 
        ON parent.EndCode = child.StartCode
)
SELECT 
    StartCode,
    StartedAt,
    EndCode,
    EndedAt
FROM RecursiveMaterialFlow
ORDER BY HierarchyLevel DESC, StartedAt;
$$ LANGUAGE sql;

-- 使用函数查询
SELECT * FROM GetMaterialFlowReport('M');
SELECT * FROM GetMaterialFlowReport('U');

验证结果 #

比如查询EndCode='M'时，会返回所有12行关联记录，每个行的EndedAt都准确对应后续流转步骤的StartedAt；查询EndCode='U'则会返回N、O、P、Q、R、S、T所有关联行，完全覆盖你需要的回溯路径。

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