SQL - 通过分组来获取总和，但如果总和为 0，则返回一行

Question

我有下表:

ID BuyOrSell Total
4    B         10
4    B         11
4    S         13
4    S         29
8    B         20
9    S         23

我想做的是为每个 ID 求 B 和 S 列的总和，如果 ID 没有 B 或 S，则有一行为 0，因此预期输出为

ID BuyOrSell Total
4    B         21
4    S         42
8    B         20
8    S          0
9    S         23
9    B          0

我已经尝试过这个，它有点像我所追求的，但不完全是:

DECLARE @Temp Table (ID int, BuyOrSell VARCHAR(1), charge Decimal)
INSERT INTO @Temp 
SELECT 4, 'B', 10 UNION ALL
SELECT 4, 'B', 11 UNION ALL
SELECT 4, 'S', 13 UNION ALL
SELECT 4, 'S', 29 UNION ALL
SELECT 8, 'B', 20 UNION ALL
SELECT 9, 'S', 23 

;With Results AS
(
SELECT ID,
       BuyOrSell,
       SUM(charge) AS TOTAL
FROM @Temp 
Group by ID, BuyOrSell
)
Select t.*,max(
                case when BuyOrSell = 'B' then 'Bfound' 
                end) over (partition by ID) as ref
            ,max(
                case when BuyOrSell = 'S' then 'Sfound' 
                end) over (partition by ID) as ref
FROM Results t;

谢谢

Answer 1

试试这个:

;WITH CTE(ID, BuyOrSell) AS(
    SELECT 
        ID, T.BuyOrSell 
    FROM @Temp
    CROSS JOIN(
        SELECT 'B' UNION ALL SELECT 'S'
    )T(BuyOrSell)
    GROUP BY ID, T.BuyOrSell
)
SELECT
    C.ID,
    C.BuyOrSell,
    Total = ISNULL(SUM(T.charge), 0)
FROM CTE C
LEFT JOIN @Temp T
    ON T.ID = C.ID
    AND T.BuyOrSell = C.BuyOrSell
GROUP BY C.ID, C.BuyOrSell
ORDER BY C.ID, C.BuyOrSell