SQL 服务器 : ROLLUP

Question

在SQL Server中使用ROLLUP时，如何获取明细行上方的小计行？

这是您在使用 ROLLUP 时通常会得到的结果:

Group                Name                 Total Sales
----------------     ---------------      ------------
Europe               France                74569.00
Europe               Germany               59456.00
Europe               United Kingdom        78327.00
Europe               NULL                 212352.00        << sub total row for Europe appears **after** all the individual rows for Europe.
North America        Northwest            208774.00
North America        Southeast            145477.00
North America        Southwest            164232.00
North America        NULL                 518483.00
Pacific              Australia             93403.00
Pacific              NULL                  93403.00

这是预期的结果集:

Group                Name                 Total Sales
----------------     ---------------      ------------
Europe               NULL                 212352.00        << sub total row for Europe needs to appear **before** the individual rows for Europe.
Europe               France                74569.00
Europe               Germany               59456.00
Europe               United Kingdom        78327.00
North America        NULL                 518483.00
North America        Northwest            208774.00
North America        Southeast            145477.00
North America        Southwest            164232.00
Pacific              NULL                  93403.00
Pacific              Australia             93403.00

使用的查询:

SELECT      [Group], [Name], SUM([SalesYTD]) AS 'Total Sales'
FROM        #TempTable
GROUP BY    [Group], [Name] WITH ROLLUP 

我们有什么想法可以得到这个输出吗？

Answer 1

您没有明确地对结果进行排序，所以当您说这是您通常会得到的结果时...欧洲的小计行出现在欧洲的所有单独行之后您只是得到幸运。

尝试对结果集进行排序:

SELECT      [Group], [Name], SUM([SalesYTD]) AS 'Total Sales'
FROM        #TempTable
GROUP BY    [Group], [Name] WITH ROLLUP 
ORDER BY    [Group], [Name]

尽管也可以尝试 not using WITH ROLLUP as well :

Non-ISO Compliant Syntax

...

WITH ROLLUP

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.