根據不同列中的不同值對多列求和

我在 Azure SQL DB 中有下表，其中包含我試圖求和的重複值。

邏輯如下：如果 PaymentID 唯一，則 Sum Payment，如果 creditID 唯一，則 sum credit，如果 debitid 唯一，則 sum debit。並採用 max(source) 這個想法是得到一個單行，每個 ID 具有不同的 ID 總和值。

例如：

158464166 | -185.15 | 0 | -3.45 | 抄送 | 5910551 | 5910548

（在上面 - 我已經採取了 min(paymentid) 讓它看起來更好

請注意，雖然在上面的程式碼片段中，Creditid 和 Debitid 只有一個重複的 ID，但它們可能有不同的 ID，因此任何程式碼都必須能夠處理它。PaymentID 將始終是唯一的。

付款、貸記和借記的值也可能不是唯一的（例如，一個 ID 支付了兩次 50 美元），因此我們不能對付款進行分組。

我做到了這一點：

SELECT id, sum(payment), sum(credit), sum(debit), max(source), creditid, debitid  
FROM (  
 SELECT *,  
        COUNT(*) OVER (PARTITION BY id) AS cnt  
 FROM Temp_Payment) AS t  
WHERE t.cnt > 1  

group by id, creditid, debitid

但它沒有給出預期的結果。

對於您發布預期結果的一行，這將返回您想要的。

將來，請將您的表格作為插入腳本發布，以便更容易使用。

SELECT
   x.*
INTO #x
FROM
(
   VALUES
       (1510142123,-589.53,0,0,'CC',5831879,NULL,NULL),
       (1510142123,-589.53,0,0,'CC',5831882,NULL,NULL),
       (1510142123,-155.06,0,0,'CC',5898896,NULL,NULL),
       (157771145,-126.42,0,0,'CC',5885900,NULL,NULL),
       (157771145,-58.73,0,0,'CC',5885903,NULL,NULL),
       (158088837,-55.14,0,-3.45,'CC',5897306,NULL,5897303),
       (158088837,-5.75,0,-3.45,'CC',5897309,NULL,5897303),
       (158464166,-161,0,-3.45,'CC',5910551,NULL,5910548),
       (158464166,-24.15,0,-3.45,'CC',5910554,NULL,5910548),
       (1591970734,-111.61,0,0,'Bank',5939648,NULL,NULL),
       (1591970734,-0.01,0,0,'Cash',5939711,NULL,NULL),
       (1591970734,-0.01,0,0,'Cash',5939714,NULL,NULL),
       (159297565,-708.93,20,0,'CC',5943728,5910848,NULL),
       (159297565,-0.02,20,0,'Cash',5948207,5910848,NULL)
)AS x (id,payment, credit, debit, [source], paymentid, creditid, debitid);

SELECT 
   x.id,
   SUM(DISTINCT y.payment) AS payment, 
   SUM(DISTINCT y.credit) AS credit, 
   SUM(DISTINCT y.debit) AS debit,
   MAX(x.source) AS source,
   MIN(x.paymentid) AS min_paymentid,
   MAX(x.debitid) AS max_debitid
FROM #x AS x
CROSS APPLY
(
   SELECT
       SUM(x2.payment) AS payment,
       SUM(x2.credit) AS credit,
       SUM(x2.debit) AS debit
   FROM #x AS x2
   WHERE x.id = x2.id
   AND   x.paymentid = x2.paymentid
   GROUP BY x2.id, 
            x2.creditid, 
            x2.debitid
) AS y
GROUP BY x.id
ORDER BY x.id;

引用自：https://dba.stackexchange.com/questions/284748