在SQL Server中使用RollUp和Group By

Question

我在SQL Server 2012 中有表Sales

Use tempdb
Go
CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)

我想创建一个具有以下结果的报告：

/*
EmpId------ Yr-----  SUM(Sales) BY EmpId, Yr----------  SUM(Sales) BY EmpId ----------SUM(Sales)
1           2005     12000.00                           12000.00                      12000.00
1           2006     18000.00                           30000.00                      30000.00
1           2007     25000.00                           55000.00                      55000.00
1           NULL                                        55000.00                      55000.00
2           2005     15000.00                           15000.00                      70000.00
2           2006     6000.00                            21000.00                      76000.00
2           NULL                                        21000.00                      76000.00
3           2006     20000.00                           20000.00                      96000.00
3           2007     24000.00                           44000.00                      120000.00
3           NULL                                        44000.00                      120000.00
NULL        NULL                                                                      120000.00
*/

我写了一个这样的查询：

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP

如何更改查询以获得更多像abauve这样的列。

Answer 1

在SQL Server 2012+中，可以使用窗口函数进行累积求和。以下基本上可以满足您的需求：

SELECT EmpId, Yr, SUM(Sales) AS Sales,
       SUM(case when Yr is not null then SUM(Sales) end) OVER
               (PARTITION BY EmpId
                Order By (case when Yr is null then 0 else 1 end) desc, Yr
               ),
       SUM(case when yr is not null then SUM(SALES) end) OVER
               (Order by EmpId, (case when Yr is null then 0 else 1 end) desc, Yr)
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP
ORDER BY (case when EmpId is null then 0 else 1 end) desc, empid,
         (case when Yr is null then 0 else 1 end) desc, yr;

这很棘手，因为rollup和窗口函数之间的相互作用需要小心。

这是SQL Fiddle。

编辑：

要修复最后一行的最后一个单元格，可以添加case语句：

SELECT EmpId, Yr, SUM(Sales) AS Sales,
       SUM(case when Yr is not null then SUM(Sales) end) OVER
               (PARTITION BY EmpId
                Order By (case when Yr is null then 0 else 1 end) desc, Yr
               ),
       (case when yr is null and empid is null
             then sum(case when yr is not null and empid is not null then sum(sales) end) over ()
             else SUM(case when yr is not null then SUM(SALES) end) OVER
                      (Order by EmpId, (case when Yr is null then 0 else 1 end) desc, Yr)
        end)
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP
ORDER BY (case when EmpId is null then 0 else 1 end) desc, empid,
         (case when Yr is null then 0 else 1 end) desc, yr;