sql-server - 当T-SQL中有重叠时如何编写case when语句

Question

我有一张这样的 table

我怎样才能把它分组到这个

Small 是 Count <25 时的计数之和； Large是Count>=25时的计数之和； Total 是所有计数的总和。

Answer 1

这样试试吧……

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    ID INT NOT NULL  PRIMARY KEY,
    nCount int NOT NULL 
    );
INSERT #TestData (ID, nCount) VALUES
    (1, 10), (2, 15), (3, 22), (4, 23),
    (5, 25), (6, 27), (7, 30);

--=====================================

WITH 
    cte_Totals AS (
        SELECT 
            Total = SUM(td.nCount),
            Small = SUM(CASE WHEN td.nCount < 25 THEN td.nCount ELSE 0 END),
            Large = SUM(CASE WHEN td.nCount >= 25 THEN td.nCount ELSE 0 END)
        FROM 
            #TestData td
        )
SELECT 
    x.[Group],
    x.[Count]
FROM 
    cte_Totals t
    CROSS APPLY (VALUES (1, 'Total', t.Total), (2, 'Small', t.Small), (3, 'Large', t.Large) ) x (SortBy, [Group],[Count])
ORDER BY 
    x.SortBy;

结果...

Group Count
----- -----------
Total 152
Small 70
Large 82

HTH,杰森