sql - 当 SQL Server 总计出现在第一行而不是表末尾时出错
问题描述
以下查询获取行和列总计。当我添加 ORDER BY 时,总列出现在第一行而不是底行。您建议的解决方案是什么?
Select
isnull (Definition_, 'GrandTotal') AS 'Definition_',
isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 1 THEN OrderQty END),0) AS January,
isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 2 THEN OrderQty END),0) AS Feburary,
isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 3 THEN OrderQty END),0) AS March,
isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 4 THEN OrderQty END),0) AS April,
isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 5 THEN OrderQty END),0) AS May,
isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 6 THEN OrderQty END),0) AS June,
isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 7 THEN OrderQty END),0) AS July,
isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 8 THEN OrderQty END),0) AS August,
isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 9 THEN OrderQty END),0) AS September,
isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 10 THEN OrderQty END),0) AS October,
isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 11 THEN OrderQty END),0) AS November,
isnull (SUM(CASE DATEPART(MONTH,DueDate) WHEN 12 THEN OrderQty END),0) AS December,
isnull (SUM(OrderQty),0) AS GrandTotal,
FROM Production.WorkOrder
GROUP BY GROUPING SETS((DATEPART(YEAR, DueDate), Definition_, ProductID),
())order by ProductID;
解决方案
grouping
如果该列是聚合的一部分,您可以使用which 返回 1 并且您可以通过以下方式在您的订单中使用它:
...
order by grouping(ProductID)
,grouping(Definition_)
,grouping(DATEPART(YEAR, DueDate))
,ProductID;
推荐阅读
- firebase - Firebase 网络应用模板语言已更改,但短信语言仍为默认语言
- ruby - rspec将实例作为参数传递导致具有不同对象ID的结果
- javascript - 如何将带时间戳的动画帧同步到音频文件?
- javascript - 在 Typescript 中动态导入一个 JS 插件模块
- .net - 错误 CA0063 : * 无法加载规则集文件 Kentor.AuthServices.ruleset' 或其依赖的规则集文件之一
- javascript - 关闭模式后如何完全退出模式
- canvas - Sharepoint powerapps 自动填充
- javascript - React Native Android 和 React Native IOS 的区别
- reactjs - 提交模态再次进行分页
- sql - 如何在两个表之间获取完全匹配的数据 - SQL 或 pandas