SQL SERVER T-SQL 按组计算小计和总计

Question

我正在尝试按组和总计将小计添加到表中。我使用以下示例重新创建了数据。

DECLARE @Sales TABLE(
        CustomerName  VARCHAR(20),
        LegalID VARCHAR(20),
        Employee VARCHAR(20),
        DocDate DATE,
        DocTotal Int,
        DueTotal Int
)
INSERT INTO @Sales SELECT 'Jhon Titor','12345', 'Employee1','2015-09-01',1000,200 
INSERT INTO @Sales SELECT 'Jhon Titor','12345', 'Employee1','2015-08-20',500,100
INSERT INTO @Sales SELECT 'Jhon Titor','12345', 'Employee1','2015-08-18',200,50 
INSERT INTO @Sales SELECT 'Deli Armstrong','2345', 'Employee1','2015-09-17',2300,700
INSERT INTO @Sales SELECT 'Deli Armstrong','2345', 'Employee1','2015-09-11',5000,1000
INSERT INTO @Sales SELECT 'Ali Mezzu','6789', 'Employee1','2015-09-07',300,200

选择 @Sales
我需要在表的最后一行添加客户小计，如下所示:

到目前为止我尝试过的:

select 
    case 
        when GROUPING(CustomerName) = 1 and
             GROUPING(Employee) = 1 and 
             GROUPING(DocDate) = 1 and
             GROUPING(LegalID) = 0 then 'Total ' + CustomerName

        when GROUPING(CustomerName) = 1 and
             GROUPING(Employee) = 1 and
             GROUPING(DocDate) =1 and
             GROUPING(LegalID) = 1 then 'Total'

        else CustomerName end as CustomerName,
    LegalID, Employee,DocDate,
    sum(DocTotal) as DocTotal,
    sum(DueTotal) as DueTotal 
From @Sales 
group by LegalID, CustomerName,Employee,DocDate with rollup

但我得到的小计为空，它应该说 Total Jhon Titor当我在查询中将它设置为静态时，它也会对每个未聚合的列 (3) 重复，

如何将小计和总计添加到上面显示的表格中？
我愿意使用没有 ROLLUP 运算符的查询。我认为可以使用工会，但不知道如何开始。
感谢您考虑我的问题。

Answer 1

我认为这就是你想要的:

select (case when GROUPING(CustomerName) = 0 and
                  GROUPING(Employee) = 1 and 
                  GROUPING(DocDate) = 1 and
                  GROUPING(LegalID) = 1
             then 'Total ' + CustomerName
             when GROUPING(CustomerName) = 1 and
                  GROUPING(Employee) = 1 and
                  GROUPING(DocDate) =1 and
                  GROUPING(LegalID) = 1 then 'Total'
             else CustomerName
        end) as CustomerName,
       LegalID, Employee,DocDate,
       sum(DocTotal) as DocTotal,
       sum(DueTotal) as DueTotal 
From @Sales 
group by grouping sets((LegalID, CustomerName ,Employee, DocDate),
                       (CustomerName),
                       ()
                      );