SQL查询:没有聚合函数时如何创建小计行 [英] SQL query: how to create subtotal rows when there is no aggregate function
问题描述
SQL 中有没有可以显示小计行的函数.我有一张这样的桌子:
Is there a function in SQL that can display subtotal rows. I have a table like this:
Date INVNUNBER CUSTOMER ITEM QTY SALES
20190630 IN3343 joe's comp 23225 2.0 3000
20190630 IN3343 joe's comp 23214 1.0 400
20190630 IN3353 matt's comp. 12222 3.0 6000
20190630 IN3353 matt's comp. 32222 3.0 3000
我尝试过 ROLLUP,但似乎 ROLLUP 需要一个聚合函数,我必须对其中一个字段求和,而所有其他字段都需要在 Group By
子句中,但我不需要真的需要任何分组:
I tried ROLLUP, but seems like ROLLUP requires an aggregate function where I have to SUM up one of the fields, and all other fields need to be in the Group By
clause, but I don't really need anything grouped:
我试过了:
SELECT DATE, INVNUMBER, CUSTOMER, ITEM, QUANTITY, SALES
FROM OESHDT
WHERE DATE = '20190630'
GROUP BY DATE, INVNUMBER, CUSTOMER WITH ROLLUP
然后我得到:
选择列表中的列OESHDT.ITEM"无效,因为它既没有包含在聚合函数中,也没有包含在 GROUP BY 子句中.
Column 'OESHDT.ITEM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
我只想像这样获取每个发票号的小计:
I just want to get the subtotals for each invoice number like this:
Date INVNUNBER CUSTOMER ITEM QTY SALES
20190630 IN3343 joe's comp 23225 2.0 3000
20190630 IN3343 joe's comp 23214 1.0 400
3.0 3400
20190630 IN3353 matt's comp. 12222 3.0 6000
20190630 IN3353 matt's comp. 32222 3.0 3000
6.0 9000
因为我没有总结任何东西,而且我只想要每个小计,SQL 能做到这一点吗?
Since I'm not summing up anything and I only want subtotals for each, can SQL do this?
推荐答案
一个选项是 Grouping Sets
示例
Declare @YourTable Table ([Date] varchar(50),[INVNUNBER] varchar(50),[CUSTOMER] varchar(50),[ITEM] varchar(50),[QTY] int,[SALES] int)
Insert Into @YourTable Values
(20190630,'IN3343','joe''s comp',23225,2.0,3000)
,(20190630,'IN3343','joe''s comp',23214,1.0,400)
,(20190630,'IN3353','matt''s comp.',12222,3.0,6000)
,(20190630,'IN3353','matt''s comp.',32222,3.0,3000)
Select Date
,InvNunber
,Customer
,Item
,Qty = sum(Qty)
,Sales = sum(Sales)
From @YourTable
Group By
Grouping Sets (
(Date,InvNunber,Customer,Item)
,(Date,InvNunber)
,(left(Date,0))
)
Order By left(Date,0) Desc
,Date
,InvNunber
,Customer Desc
退货
Date InvNunber Customer Item Qty Sales
20190630 IN3343 joe's comp 23214 1 400
20190630 IN3343 joe's comp 23225 2 3000
20190630 IN3343 NULL NULL 3 3400
20190630 IN3353 matt's comp. 12222 3 6000
20190630 IN3353 matt's comp. 32222 3 3000
20190630 IN3353 NULL NULL 6 9000
NULL NULL NULL NULL 9 12400
这篇关于SQL查询:没有聚合函数时如何创建小计行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!