Server:汇总

Question

在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]

虽然也尝试过 as well

不符合ISO标准的语法 ..。 卷起 此功能将在Microsoft Server的未来版本中删除。避免在新的开发工作中使用此功能，并计划修改当前使用此功能的应用程序。