通过一个原材料代码找到多个成品代码

Question

在工作中的SQL server上，我们有多个部分代码，它们以"M“或"P”代码开头，一旦这些M或P代码在整个制造过程中被使用，这些代码就被分配给一个"A“代码，其中可能有多个"A”代码，最终被出售为"R“代码。

因此，我希望输入一个特定的M代码，例如M1655，它直接链接到R代码，而不必通过每个A代码手动搜索。

例如，M1655的初始搜索结果在A02748中。然后，我需要搜索A02748，然后搜索到A02749，然后继续搜索R3700、R3856和R3857。

我只是不知道该如何分类，下面有一些数据

Resource    PM Name   PM Description
M1655       A02748    HONEY RST SALMON FROZEN
A02748      A02749    HONEY RST SALMON DEFROSTED
A02749      R3770     LOCHMUIR SALMON PASTA
A02749      R3856     CAFE HONEY RST SALMON NICOISE
A02749      R3857     BFY HOT SALMON & SUPERGREEN

获取此信息的当前SQL查询详细信息如下：

SELECT pdResc.CalcValResource AS [Consumed Resource], pdPM.PM AS [PM Name], pdBalConsRescDt.Shrinkage,
        pdPM.Description AS [PM Description], fdBasResc.CatCodesCode2 AS [Resource BUSINESS], pdPM.PMSite AS [Site Name],
        fdBasResc_1.CatCodesCode2 AS [PM BUSINESS]
    FROM ERP_STD_CALE.dbo.fdBasResc AS fdBasResc,
        ERP_STD_CALE.dbo.fdBasResc AS fdBasResc_1,
        ERP_STD_CALE.dbo.pdBalConsRescDt AS pdBalConsRescDt,
        ERP_STD_CALE.dbo.pdPM AS pdPM,
        ERP_STD_CALE.dbo.pdResc AS pdResc,
        ERP_STD_CALE.dbo.pdStage AS pdStage
    WHERE pdPM.ObjectID = pdStage.ParentObjectID
        AND pdResc.ParentObjectID = pdStage.ObjectID
        AND pdResc.RescDtlObjectID = pdBalConsRescDt.ObjectID
        AND pdResc.CalcValRescOID = fdBasResc.ObjectID
        AND pdPM.PPRObjectID = fdBasResc_1.ObjectID
        AND (
              ( pdStage.ParentClassID = 10353 )
              AND ( pdStage.CollectionID = 1 )
              AND ( pdResc.ParentClassID = 10354 )
              AND ( pdResc.CollectionID = 1 )
              AND ( pdPM.PMInstType = 0 )
              AND ( pdPM.PMSite = '1000' )
              AND ( pdPM.AvailforSched = 1 )
            )
    ORDER BY fdBasResc.CatCodesCode2, pdResc.CalcValResource;

Answer 1

像这样的东西有用吗？这是一个递归的CTE。

DECLARE @crap TABLE (Resource VARCHAR(6), PMName VARCHAR(6), PMDawn VARCHAR(50))
INSERT @crap ( Resource, PMName, PMDawn )
SELECT *
FROM (
VALUES('M1655' ,      'A02748',    'HONEY RST SALMON FROZEN'),
      ('A02748',      'A02749',    'HONEY RST SALMON DEFROSTED'),
      ('A02749',      'R3770' ,    'LOCHMUIR SALMON PASTA'),
      ('A02749',      'R3856' ,    'CAFE HONEY RST SALMON NICOISE'),
      ('A02749',      'R3857' ,    'BFY HOT SALMON & SUPERGREEN')
) x (Resource, PMName, PMDawn);


WITH r_cte AS (
        SELECT  c.Resource, c.PMName, 0 AS PMLevel, CONVERT(VARCHAR(900), c.Resource) AS FullPath
        FROM @crap AS c
        WHERE c.Resource = 'M1655'

        UNION ALL

        SELECT c2.Resource, c2.PMName, PMLevel + 1, CONVERT(VARCHAR(900), CONCAT(r.FullPath, '/', c2.Resource))
        FROM r_cte r
        JOIN @crap AS c2
        ON r.PMName = c2.Resource
)
SELECT DISTINCT c.PMName, c.PMDawn
FROM r_cte r
JOIN @crap AS c
ON r.Resource = c.Resource
WHERE c.PMName LIKE 'R%'
OPTION(MAXRECURSION 0);