我需要按 2 列（BillingId 和 PaymentType）對資料進行分組——這沒有問題，并且輸出 1 行的列具有唯一的 PaymentType——這一步有問題。所以每個 BillingId 只有 1 行，該表將用于連接資料庫中的另一個表。
計費表：
BillingId (unique)
12345
67890
付款表：
PaymentId PaymentType BillingId PaymentAmount
(unique)
12 electronic 12345 62.29
14 electronic 12345 73.28
56 electronic 12345 -62.29
6 electronic 67890 83.58
2 adjustment 67890 30.43
我的代碼：
SELECT GroupedTable.*
FROM (SELECT b.BillingId,
p.PaymentType,
SUM(p.PaymentAmount) AS AmountPaid
FROM Billing AS b
LEFT JOIN Payment AS p
ON (b.BillingId = p.BillingId)
GROUP BY b.BillingId, p.PaymentType) AS GroupedTable
輸出（顯然不正確）：
BillingId PaymentType AmountPaid
67890 electronic 83.58
12345 electronic 73.28
67890 adjustment 30.43
我需要的輸出：
BillingId AmountPaid AmountAdjusted
(electronic) (adjustment)
67890 83.58 30.43
12345 73.28 0
uj5u.com熱心網友回復：
如果您使用Case When如下運算式，看起來會更容易：
Select B.BillingId, Sum(Case When P.PaymentType='electronic' Then P.PaymentAmount End) As [AmountPaid (electronic)],
Sum(Case When P.PaymentType='adjustment' Then P.PaymentAmount End) As [AmountAdjusted (adjustment)]
From Billing As B Left Join Payment As P On (B.BillingId=P.BillingId)
Group by B.BillingId
db<>小提琴
| 帳單編號 | 支付金額（電子版） | AmountAdjusted（調整） |
|---|---|---|
| 12345 | 73,28 | 空值 |
| 67890 | 83,58 | 30,43 |
uj5u.com熱心網友回復：
您應該BillingId只分組并使用條件聚合：
SELECT b.BillingId,
SUM(CASE WHEN p.PaymentType = 'electronic' THEN p.PaymentAmount ELSE 0 END) AS AmountPaid,
SUM(CASE WHEN p.PaymentType = 'adjustment' THEN p.PaymentAmount ELSE 0 END) AS AmountAdjusted
FROM Billing AS b LEFT JOIN Payment AS p
ON b.BillingId = p.BillingId
GROUP BY b.BillingId;
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/419755.html
標籤：
上一篇：去統計動態url呼叫