SQLServerRETURN按順序遞回查詢

我有一個遞回查詢，它輸出一個 BOM（材料清單），它確實輸出了所有級別。但是問題是在呼叫該程序時它是無序的，這是在訪問和 SQL Server 中。如果沒有子部件，則回傳該部件是空的。

代碼

創建/更改表：

USE [<Database>]

SET ANSI_NULLS ON
GO    

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE BOM
(
    [POSITION] INT CHECK ([POSITION] >= 0),
    [PARENT] VARCHAR(10),
    [PART] VARCHAR(10),
    [QUANTITY] INT NOT NULL
) ON [PRIMARY]
GO

創建/更改程序：

USE [<Database>]

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description: GETS BOM GIVEN PARAMETER
-- =============================================
CREATE PROCEDURE GETBOM 
    @TL VARCHAR(10)
AS
BEGIN
    SET NOCOUNT ON;

    WITH TESTBOM AS
    (
        SELECT 
            [POSITION], [PARENT], [PART], [QUANTITY]
        FROM 
            BOM
        WHERE  
            [PART] = @TL 
            AND [Parent] IS NULL
        UNION ALL
        SELECT 
            E.[POSITION], E.[PARENT], E.[PART], E.[QUANTITY]
        FROM 
            BOM E  
        INNER JOIN 
            TESTBOM M ON E.[PARENT] = M.[PART] 
    )
    SELECT * FROM TESTBOM
END
GO

樣本資料：

輸出

答：

EXEC GETBOM 'A' 

乙：

EXEC GETBOM 'B' 

C

EXEC GETBOM 'C' 

D

EXEC GETBOM 'D' 

乙

EXEC GETBOM 'E' 

uj5u.com熱心網友回復：

如果沒有適當的樣本資料，很難在這里準確地說出您需要什么，但似乎如果您將每個專案的位置與其所有父項連接起來，您就可以按此排序。

CREATE OR ALTER PROCEDURE GETBOM 
@TL VARCHAR(10)

AS

SET NOCOUNT ON;

WITH TESTBOM AS
(
    SELECT POSITION, PARENT, PART, QUANTITY, CAST(POSITION AS nvarchar(100)) AS Ordering
    FROM BOM
    WHERE [PART] = @TL
      and [Parent] is null

    UNION ALL

    SELECT E.[POSITION],E.[PARENT],E.[PART],E.[QUANTITY], CAST(CONCAT(M.Ordering, E.POSITION) AS nvarchar(200)) 
    FROM BOM E
    INNER JOIN TESTBOM M ON E.[PARENT]=M.[PART] 
)
SELECT
  POSITION, PARENT, PART, QUANTITY
FROM TESTBOM
ORDER BY
  Ordering;

uj5u.com熱心網友回復：

以下代碼hierarchyid在遞回組裝 BoM 時組裝 a ( dbfiddle )。

樣本資料：

-- Sample data.
declare @BoM as table (
  Position Int Check ( Position >= 0 ),
  Parent VarChar(10),
  Part VarChar(10),
  Quantity Int not NULL );

insert into @BoM ( Position, Parent, Part, Quantity ) values
  ( 0, NULL, 'A', 1 ),
  ( 1, 'A', 'B', 1 ),
  ( 0, NULL, 'B', 1 ),
  ( 1, 'B', 'E', 1 ),
  ( 2, 'B', 'F', 3 ), -- Changed quantity from 1 to 3 to show extended quantity.
  ( 0, NULL, 'F', 1 ),
  ( 1, 'F', 'K', 1 ),
  ( 2, 'F', 'C', 1 ),
  ( 0, NULL, 'C', 1 ),
  ( 1, 'C', 'G', 2 ),
  ( 2, 'C', 'H', 1 ),
  ( 2, 'A', 'C', 1 ),
  ( 3, 'A', 'D', 1 ),
  ( 0, NULL, 'D', 1 ),
  ( 1, 'D', 'I', 1 ),
  ( 0, NULL, 'I', 1 ),
  ( 1, 'I', 'L',  1 ),
  ( 2, 'I', 'M', 2 ),
  ( 2, 'D', 'J', 1 );

select * from @BoM;

遞回查詢：

-- Starting point for building a BoM.
declare @BasePart as VarChar(10) = 'A';

-- Do the deed.
with
  BoMTree as (
    -- Start with the parentless base part ...
    select Position, Parent, Part, Quantity,
      -- Many BoMs provide the extended quantity for subcomponents.
      Quantity as ExtendedQuantity,
      -- Build a hierarchy to keep track of the order of parts within the BoM.
      Cast( '/'   Cast( Row_Number() over ( order by Position ) as VarChar(4) )   '/' as VarChar(100) ) as BoMOrder
      from @BoM
      where Part = @BasePart and Parent is NULL
    union all
    -- ... and add additional levels of parts one at a time.
    select B.Position, B.Parent, B.Part, B.Quantity,
      -- Extend the quantity.
      B.Quantity * BT.ExtendedQuantity,
      -- Add another level to the hierarchy.
      Cast( BT.BoMOrder   Cast( Row_Number() over ( order by B.Position ) as VarChar(4) )   '/' as VarChar(100) ) as BoMOrder
      from @BoM as B inner join
        BoMTree as BT on B.Parent = BT.Part
    ),
  BoMTreeWithOrder as (
    -- Cast the order from a string to a proper hierarchy id.
    select Position, Parent, Part, Quantity, ExtendedQuantity,
      Cast( BoMOrder as HierarchyId ) as BoMOrderHI
      from BoMTree )
  -- Display the indented BoM.
  select Position, Parent,
    Space( ( BoMOrderHI.GetLevel() - 1 ) * 2 )   Part as Part,
    Quantity,
    ExtendedQuantity, BoMOrderHI
    from BomTreeWithOrder
    order by BoMOrderHI;

標籤：sql服务器 tsql ms-access-2010

上一篇：來自不同表的多個列上的sqlserver2019外鍵

下一篇：無法使用物體框架CLI更新資料庫架構