SQL 查询 : how to create subtotal rows when there is no aggregate function

Question

SQL中有没有可以显示小计行的函数。我有一个这样的表:

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 子句中，但我没有真的不需要任何分组:

我试过:

SELECT DATE, INVNUMBER, CUSTOMER, ITEM, QUANTITY, SALES
FROM OESHDT
WHERE DATE = '20190630'
GROUP BY DATE, INVNUMBER, CUSTOMER WITH ROLLUP

然后我得到:

Column 'OESHDT.ITEM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

我只想像这样获取每个发票编号的小计:

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 可以做到这一点吗？

Answer 1

一个选项是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