SQLServer2017中每一層的遞回求和-有解無憂

我需要找出樹的每個節點的遞回和。請找到下面的圖片。

這里藍色的數字（假設沒有銷售）是父數量的總和其子代的總和，紅色的數字是節點本身的銷售。所以如果我們以Node C = 44為例，那么它的計算就是這樣的。

節點 C有一個孩子F并且F又有兩個孩子G和H。G & H沒有孩子。但他們分別取得了5 和 6的銷售額。而且他們的父母F自己也做了11 個銷售，所以F 的總銷售額將是 22 [11 個他自己的，11 個來自他的孩子]。同樣在同一行C，F的父級，也獲得了自己的22 銷售額。所以現在C 的總銷售額將是44（他自己的 22 和他的孩子和孫輩的 22）。

這種層次結構和計算將繼續進行。層次結構的深度和廣度也沒有限制。一個節點可以有 N 個孩子。

SQL Server 2017 中每一層的遞回求和

我的資料庫有 3 個表，如下圖所示：

SQL Server 2017 中每一層的遞回求和

StoreMaster：此表包含所有商店的主串列。
StoreMaps：這是一個映射表，用于維護父子層次結構。這里MasterStoreId -> ParentId和SlaveStoreId -> ChildId。masterstoreid 和 slavestoreid 都參考了StoreMaster表的storeid 。此外，此映射表支持任何層次結構中的 N 級子級。完全沒有限制。
StoreSales：StoreMaster 的交易表。此表包含商店特定的銷售額。

我想實作的是，我需要一個遞回查詢，這將有助于找到我TotalAmount，并Quantity of Sales如圖1以上]由每個店制作。

我嘗試使用 CTE 但沒有獲得所需的輸出。這是我嘗試過的

;WITH ChildStores AS 
(
    SELECT SlaveStoreId
    FROM StoreMaps
    WHERE SlaveStoreId = 35
        
    UNION ALL
        
    SELECT t.SlaveStoreId
    FROM StoreMaps t
    INNER JOIN ChildStores r ON t.MasterStoreId = r.SlaveStoreId
)
INSERT INTO @StoreTable
    SELECT *
    FROM ChildStores

SELECT DISTINCT ss.StoreId, ss.StoreCode, ss.StoreName  
FROM StoreSales ss 
WHERE ss.StoreId IN (SELECT * FROM @StoreTable)

SELECT ss.StoreName, COUNT(ss.SaleId), SUM(ss.AmountPaid) 
FROM StoreSales ss 
WHERE ss.StoreId IN (SELECT * FROM @StoreTable)  
  AND ss.SaleStatus = 'Paid' 
  AND ss.PlanCode <> '12345'
GROUP BY ss.StoreName

第二個查詢：

WITH cteAggregateCost AS 
(  
    SELECT 
        i.SlaveStoreId AS rootid, i.SlaveStoreId, i.MasterStoreId
    FROM
        StoreMaps  i 

    UNION ALL

    SELECT 
        rootid, i.SlaveStoreId, i.MasterStoreId
    FROM
        StoreMaps i 
    JOIN
        cteAggregateCost c ON i.MasterStoreId = c.SlaveStoreId
)
SELECT
    t.StoreName, SUM(t.AmountPaid) AS AggregateCost
FROM
    StoreMaps a
LEFT JOIN
    cteAggregateCost i ON a.SlaveStoreId = i.rootid 
LEFT JOIN 
    StoreSales t ON i.SlaveStoreId = t.StoreId
WHERE 
    t.SaleStatus = 'Paid' 
    AND t.PlanCode <> 12345
GROUP BY
    t.StoreName

架構

CREATE TABLE StoreMaps
(
    [StoreMapId] [int] IDENTITY(1,1) NOT NULL,
    [MasterStoreId] [int] NULL,
    [MasterStoreCode] [varchar](10) NULL,
    [SlaveStoreId] [int] NULL,
    [SlaveStoreCode] [varchar](10) NULL,
    [IsActive] [bit] NOT NULL,

    CONSTRAINT [PK_StoreMaps] 
        PRIMARY KEY CLUSTERED ([StoreMapId] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE StoreMaster
(
    [StoreId] [int] IDENTITY(1,1) NOT NULL,
    [StoreName] [varchar](150) NULL,
    [StoreCode] [varchar](10) NULL,
    [IsActive] [bit] NOT NULL,
    
    CONSTRAINT [PK_StoreMaster] 
        PRIMARY KEY CLUSTERED ([StoreId] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE StoreSales
(
    [SaleId] [int] IDENTITY(1,1) NOT NULL,
    [StoreId] [int] NULL,
    [StoreCode] [varchar](10) NULL,
    [StoreName] [varchar](150) NULL,
    [SaleStatus] [varchar](20) NULL,
    [AmountPaid] [int] NULL,
    [IsActive] [bit] NOT NULL,

    CONSTRAINT [PK_StoreSales] 
        PRIMARY KEY CLUSTERED ([SaleId] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,  
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

完整查詢

DECLARE @StoreCode VARCHAR(10) = NULL, 
        @StartDate DATE = NULL, @EndDate DATE = NULL

SET NOCOUNT ON;

DECLARE @Description VARCHAR(50), 
        @NetSales DECIMAL(16, 2), 
        @Quantity TINYINT, 
        @PayoutPercent TINYINT, @PayableAmount DECIMAL(16, 2), 
        @StoreName VARCHAR(150), @PartnerId TINYINT, 
        @Rowcount INT, @BillingMonth VARCHAR(10)

DECLARE @StoreTable AS TABLE (StoreId INT);

--INSERT INTO SpDebug (ObjectName, ObjectParam)
--VALUES (OBJECT_NAME(@@PROCID), @JsonParams)
IF ISNULL(TRIM(@StoreCode), '') = ''
BEGIN
    SET @StoreCode = 'GW'
END

IF ISNULL(@StartDate, '') = ''
BEGIN
    SET @StartDate = DATEADD(DD, - 7, GETDATE());
END

IF ISNULL(@EndDate, '') = ''
BEGIN
    --Select * from @StoreTable
    SET @EndDate = DATEADD(dd, - 1, GETDATE());
END

BEGIN TRY
    BEGIN TRANSACTION;

    WITH ChildStores
    AS (
        SELECT SlaveStoreId
        FROM StoreMaps
        WHERE SlaveStoreCode = @StoreCode
        
        UNION ALL
        
        SELECT t.SlaveStoreId
        FROM StoreMaps t
        INNER JOIN ChildStores r
            ON t.MasterStoreId = r.SlaveStoreId
        )
    INSERT INTO @StoreTable
    SELECT *
    FROM ChildStores

    SELECT @Rowcount = COUNT(*)
    FROM @StoreTable;

    WHILE (@Rowcount > 0)
    BEGIN
        SELECT @Rowcount = @Rowcount - 1;

        SELECT @PartnerId = StoreId
        FROM @StoreTable
        ORDER BY StoreId DESC OFFSET @Rowcount ROWS

        FETCH NEXT 1 ROWS ONLY;

        IF EXISTS (
                SELECT 1
                FROM StoreMaster
                WHERE StoreId = @PartnerId
                    AND IsActive = 1
                    AND ExcludeFromPayout = 0
                )
        BEGIN
            SELECT @PayoutPercent = PayoutPercent, @StoreName = StoreName, @StoreCode = StoreCode
            FROM StoreMaster
            WHERE StoreId = @PartnerId
            

            SET @Description = CONCAT ('Payout for ', FORMAT(@StartDate, 'dd-MMM-yyyy'), ' & ', FORMAT(@EndDate, 'dd-MMM-yyyy'));

            SELECT @Quantity = COUNT(SaleId), @NetSales = SUM(AmountPaid / 1.18)
            FROM StoreSales
            WHERE StoreId = @PartnerId
                AND (
                    CAST(ModifiedOn AS DATE) BETWEEN CAST(@StartDate AS DATE)
                        AND CAST(@EndDate AS DATE)
                    )
                AND SaleStatus = 'Paid'
                AND PlanCode <> '12345'

            IF NOT EXISTS (
                    SELECT *
                    FROM PartnerInvoices
                    WHERE (
                            (
                                @StartDate BETWEEN PayoutStartDate
                                    AND PayoutEndDate
                                )
                            OR (
                                @EndDate BETWEEN PayoutStartDate
                                    AND PayoutEndDate
                                )
                            )
                        AND PartnerId = @PartnerId
                    )
            BEGIN
                SET @Quantity = ISNULL(@Quantity, 0)
                SET @NetSales = ISNULL(@NetSales, 0)
                SET @PayableAmount = ISNULL((@NetSales * @PayoutPercent * 0.01), 0)
                SET @BillingMonth = FORMAT(GETDATE(), 'MMyyyy')

                INSERT INTO PartnerInvoices (PartnerId, StoreName, PayoutStartDate, PayoutEndDate, BillingMonth, TxnRefNumber, [Description], NetSales, Quantity, PayoutPercent, PayableAmount)
                VALUES (@PartnerId, @StoreName, @StartDate, @EndDate, @BillingMonth, '', @Description, @NetSales, @Quantity, @PayoutPercent, @PayableAmount)
            END
                    --ELSE
                    --BEGIN
                    --  PRINT 'Invoice already generated for this duration.';
                    --END
        END
    END

    COMMIT TRANSACTION

    SELECT 1;
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION

    INSERT INTO AppErrors (ErrorMessage, ObjectName, [Parameters])
    VALUES (ERROR_MESSAGE(), OBJECT_NAME(@@procid), @PartnerId);

    THROW
END CATCH

請幫我解決這個查詢，因為這是提取我們渠道合作伙伴的支出，我不能花太多時間進行研發。這是一個嚴重的錯誤。

uj5u.com熱心網友回復：

你正在尋找這樣的東西嗎？

WITH CTE AS (
    SELECT NULL AS MasterID, @MasterStoreID AS SlaveID
    UNION ALL
    SELECT M.MasterStoreId, M.SlaveStoreId AS SlaveID
    FROM CTE C
        INNER   JOIN StoreMaps M ON M.MasterStoreId = C.SlaveID
)
SELECT @MasterStoreID AS StoreID, SUM(ISNULL(S.AmountPaid, 0)) AS AmountPaidTotal
FROM CTE C
    LEFT    JOIN StoreSales ON S.StoreId = C.SlaveID;

@MasterStoreID您想要總銷售額的商店的 ID在哪里。

編輯：此查詢將回傳表中AmountPaid每個商店的總和StoreMaster，不帶@MasterStoreID引數：

WITH CTE AS (
    SELECT StoreID AS RootID, NULL AS MasterID, StoreID AS SlaveID
    FROM StoreMaster
    UNION ALL
    SELECT C.RootID, M.MasterStoreid, M.SlaveStoreId AS SlaveID
    FROM CTE C
        INNER   JOIN StoreMaps M ON M.MasterStoreid = C.SlaveID
)
SELECT C.RootID AS StoreID, SUM(ISNULL(S.AmountPaid, 0)) AS AmountPaidTotal
FROM CTE C
    LEFT    JOIN StoreSales S ON S.StoreId = C.SlaveID
GROUP BY C.RootID;

轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/393007.html

標籤：sql-server 查询语句公用表表达式递归查询 sql-server-2017

上一篇：為什么相同事務的兩個并行執行之間會出現死鎖？

下一篇：將子查詢轉換為JSON性能