SQL Server-具有2个表的CTE，直到消耗完数量

问题描述

我尝试对我的2个表（SQL Server 2012）执行递归联接，如下所示：

I am try to perform a recursive join of my 2 tables (SQL Server 2012) like below:

表：购买

szProductID    nQty     szSupplierCode
0001           5        A-101
0001           50       A-102
0001           2        A-103
0001           70       A-104

和

表：销售

szProductID     nQty     szSalesID
0001            10       S-101
0001            20       S-102
0001            20       S-103
0001            50       S-104

我需要这样的结果：

szProductID      nQtySales      SupplierCode     SalesID
0001             5              A-101            S-101
0001             5              A-102            S-101
0001             20             A-102            S-102
0001             20             A-102            S-103
0001             5              A-102            S-104
0001             2              A-103            S-104
0001             43             A-104            S-104

目标是查找szSupplierCode出售了多少商品。我已经找到了很多进行选择的示例，但是我不确定CTE是否可以解决我的问题。

The goal is to find how many item sold by szSupplierCode. I've found plenty of examples for doing selects but i'm not sure if CTE can solve my problem.

如果有人可以确认使用CTE或游标可以做到这一点，我将不胜感激。

If anyone can confirm this is possible with a CTE or cursor, I'd appreciate it.

谢谢！

推荐答案

您可以使用：

;WITH PurchaseRN AS (
   -- Add row number field to Purchase table
   SELECT szProductID, nQty, szSupplierCode,
          ROW_NUMBER() OVER (PARTITION BY szProductID 
                             ORDER BY szSupplierCode) AS rn
   FROM Purchase
), SalesRN AS (
   -- Add row number field to Sales table
   SELECT szProductID, nQty, szSalesID,
          ROW_NUMBER() OVER (PARTITION BY szProductID 
                             ORDER BY szSalesID) AS rn
   FROM Sales
), ConsumePurchases AS (
   -- Consume 1st Sales record using 1st Purchase record
   SELECT p.szProductID, 
          IIF(p.nQty > s.nQty, s.nQty, p.nQty) AS nQtySales,
          p.szSupplierCode AS SupplierCode, 
          s.szSalesID AS SalesID,         
          -- Propagate un-consumed Purchase/Sales quantities to next recursion level
          IIF(p.nQty > s.nQty, p.nQty - s.nQty, 0) AS pResidue,
          IIF(p.nQty > s.nQty, 0, s.nQty- p.nQty) AS sResidue,
          -- Purchase row number processed by current recursion level
          1 AS prn, 
          -- Sales row number processed by current recursion level
          1 AS srn
   FROM PurchaseRN AS p
   INNER JOIN SalesRN AS s ON p.szProductID = s.szProductID 
   WHERE p.rn = 1 AND s.rn = 1

   UNION ALL

   SELECT p.szProductID, 
          -- Calculate Sales quantity consumed by current recursion level
          -- If un-consumed Purchase/Sales quantities exist from previous level
          -- then use this instead of nQty field.
          IIF(c.pResidue > 0, 
             IIF(c.pResidue > s.nQty, s.nQty, c.pResidue),
             IIF(c.sResidue > 0, 
               IIF(p.nQty > c.sResidue, c.sResidue, p.nQty),
               IIF(p.nQty > s.nQty, s.nQty, p.nQty))) AS nQtySales,
          p.szSupplierCode AS SupplierCode, 
          s.szSalesID AS SalesID,         
          x.pResidue,
          x.sResidue, 
          x.prn AS prn, 
          x.srn AS srn
   FROM PurchaseRN AS p
   INNER JOIN SalesRN AS s ON p.szProductID = s.szProductID 
   INNER JOIN ConsumePurchases AS c ON c.szProductID = s.szProductID 
   CROSS APPLY (
      SELECT -- if previous Purchare record is not fully consumed (c.pResidue > 0)
             -- then stay at the same Purchase record (c.prn), else get next record.
             CASE 
                WHEN c.pResidue > 0 THEN c.prn
                ELSE c.prn + 1
             END AS prn,
             -- if previous Sales record is not fully consumed (c.sResidue > 0)
             -- then stay at the same Sales record (c.srn), else get next record.
             CASE 
                WHEN c.sResidue > 0 THEN c.srn 
                ELSE c.srn + 1
             END AS srn,             
             -- calculate Sales quantity left un-cosumed (sResidue) after current record 
             -- has been processed
             CASE 
                WHEN c.sResidue > 0 THEN IIF(c.sResidue - p.nQty > 0, c.sResidue - p.nQty, 0)
                WHEN c.pResidue > 0 THEN IIF(c.pResidue > s.nQty, 0, s.nQty - c.pResidue)                
                ELSE IIF(p.nQty > s.nQty, p.nQty - s.nQty, 0)
             END AS sResidue, 
             -- calculate Purchase quantity left un-cosumed (pResidue) after current record 
             -- has been processed
             CASE 
                WHEN c.pResidue > 0 THEN IIF(c.pResidue - s.nQty > 0, c.pResidue - s.nQty, 0)
                WHEN c.sResidue > 0 THEN IIF(p.nQty > c.sResidue, p.nQty - c.sResidue, 0) 
                ELSE IIF(p.nQty > s.nQty, p.nQty - s.nQty, 0) 
             END AS pResidue) AS x(prn, srn, sResidue, pResidue)
    -- Continue until there are no more Purchase/Sales records to process
    WHERE p.rn = x.prn AND s.rn = x.srn 
)
SELECT szProductID, nQtySales, SupplierCode, SalesID
FROM ConsumePurchases

这篇关于SQL Server-具有2个表的CTE，直到消耗完数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！