匯總SQL資料庫中存在多對多關系的兩列

我有以下資料庫表Transactions和Payments. 資料的結構使得交易和付款都具有多對多關系，它們在查詢中連接在一起，如下所示：

SELECT Transactions.Id, Payments.Id, Transactions.Amount
FROM Transactions 
LEFT JOIN Payments ON Transactions.Id = Payments.TransactionId 

| Transactions.Id | Payments.PaymentId | Transactions.Amount |
| ----------------| -------------------| --------------------|
| 3492            | 123456             | 123                 |
| 3492            | 123457             | 123                 |
| 3493            | 123458             | 300                 |
| 3494            | 123459             | 10                  |
| 3495            | 123459             | 25                  |

我希望能夠將表簡化為如下所示（如果這在 SQL 中是可能的）

| Transactions.Id | Payments.PaymentId | Transactions.Amount |
| ----------------| -------------------| --------------------|
| 3492            | 123456, 123457     | 123                 |
| 3493            | 123458             | 300                 |
| 3494, 3495      | 123459             | 35                  |

...甚至沒有第三列（如果它降低了復雜性）

| Transactions.Id | Payments.PaymentId |
| ----------------| -------------------|
| 3492            | 123456, 123457     |
| 3493            | 123458             |
| 3494, 3495      | 123459             |

到目前為止我所擁有的：

SELECT
Transactions.Id, 
   STUFF((SELECT '; '   CAST(Payments.Id as varchar)
    FROM Payments 
    WHERE Payments.TransactionId = Transactions.Id
    FOR XML PATH('')), 1, 1, '') [PaymentIds]
FROM Transactions

| Transactions.Id | PaymentIds         |
| ----------------| -------------------|
| 3492            | 123456, 123457     |
| 3493            | 123458             |
| 3494            | 123459             |
| 3495            | 123459             |

如何將 Transactions.Id 添加到逗號分隔串列中？我正在使用 SQL Server 12.0.2000.8

SQLFiddle在這里

uj5u.com熱心網友回復：

基本上，您必須通過兩次才能匯總每筆交易的付款，然后匯總每筆付款的交易。這假設（基于樣本資料和所需的輸出）當付款適用于多個交易時，您需要總和，但是當交易有多個付款時，您需要最大值（或者在這種情況下它們總是相同的max 作業正常嗎？）：

;WITH FirstPass AS 
(
  SELECT t.Id, pId = STUFF(
    (SELECT ', '   CONVERT(varchar(11), p.Id)
      FROM dbo.Payments AS p
      WHERE p.TransactionId = t.Id
      ORDER BY p.Id
      FOR XML PATH(''), TYPE).value(N'./text()[1]', 
        N'varchar(max)'), 1, 2, ''),
    Amount = MAX(t.Amount)
  FROM dbo.Transactions AS t
  GROUP BY t.Id
)
SELECT [Transactions.Id] = STUFF(
  (SELECT ', '   CONVERT(varchar(11), fp.Id)
    FROM FirstPass AS fp
    WHERE fp.pId = FirstPass.pId
    ORDER BY fp.pId
    FOR XML PATH(''), TYPE).value(N'./text()[1]', 
        N'varchar(max)'), 1, 2, ''),
  [Payments.PaymentId] = FirstPass.pId,
  Amount = SUM(FirstPass.Amount)
FROM FirstPass 
GROUP BY FirstPass.pId;

輸出：

• 示例db<>fiddle

在 SQL Server 2017 （或 Azure SQL 資料庫）上，這變得更加簡單，盡管對于這個特定的用例來說仍然有點復雜。這對你現在沒有幫助，但可以幫助今天的其他讀者、未來的讀者，甚至未來的你：

;WITH FirstPass AS 
(
  SELECT t.Id, ca.pId, Amount = MAX(t.Amount)
  FROM dbo.Transactions AS t
  CROSS APPLY
  (
    SELECT pId = STRING_AGG(p.Id, ', ')
      FROM dbo.Payments AS p
      WHERE p.TransactionId = t.Id
  ) AS ca GROUP BY t.Id, ca.pId
)
SELECT [Transactions.Id] = STRING_AGG(Id, ', '),
  [Payments.PaymentId] = pId,
  Amount = SUM(Amount)
FROM FirstPass 
GROUP BY pId;

相同的輸出：

• 示例db<>fiddle

uj5u.com熱心網友回復：

以下代碼生成您想要的輸出

WITH cte1 AS(
   SELECT 
       Transactions.Id AS TransactionID, 
       Payments.Id AS PaymentID,
       max(Transactions.amount) Amount
   FROM Transactions 
   LEFT JOIN Payments ON Transactions.Id = Payments.TransactionId 
   GROUP BY Transactions.Id,Payments.Id),
   
   
cte2 AS(
  SELECT

       STUFF((SELECT ','   cast(t2.TransactionID as varchar(100)) 
       FROM cte1 t2
       WHERE t2.PaymentID = t1.PaymentID
       FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(MAX)'),1,1,'')TransactionID,
     
       STUFF((SELECT ','   cast(t2.PaymentID as varchar(100)) 
       FROM cte1 t2
       WHERE t2.TransactionID = t1.TransactionID
       FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(MAX)'),1,1,'')PaymentID,
     
       (SELECT sum(Amount)
       FROM cte1 t2 
       WHERE t2.PaymentID = t1.PaymentID
       GROUP BY PaymentID)Amount
     
    
     
   FROM cte1 t1
)

SELECT TransactionID,PaymentID,Amount
FROM cte2
GROUP BY TransactionID,PaymentID,Amount

結果

標籤：

上一篇：如何在OPENQUERY中使用Declare變數

下一篇：如何使用TSQL決議JSON