select no,date,charge,money,sum(money) from invoice group by no,date,charge,money,no with rollup having grouping(no)=0 and grouping(date)=0 and grouping(charge)=0 and grouping(money)=0
CREATE TABLE #INVOICE ( [NO] INT,[DATE] DATETIME,CHARGE NVARCHAR(200),[MONEY] MONEY) GO INSERT #INVOICE VALUES(1,' 2003-05-09 00:00:00',N'材料费', 234.0000) INSERT #INVOICE VALUES(1,' 2003-05-09 00:00:00',N'上门服务费', 12.0000) INSERT #INVOICE VALUES(2,'2003-05-09 00:00:00',N'材料费',8.0000) INSERT #INVOICE VALUES(2,'2003-05-09 00:00:00',N'上门服务费',100.0000) GO select no,date,charge,sum(money) AS MONEY from #invoice group by no,date,charge WITH CUBE having grouping(no)=0 and grouping(date)=0 GO DROP TABLE #INVOICE
select no,date,charge,money,sum(money) from invoice group by no,date,charge,money,no with rollup where not(no is null and date is null and charge is null and money )
group by no,date,charge,money,no with rollup
having grouping(no)=0 and grouping(date)=0 and grouping(charge)=0 and grouping(money)=0
( [NO] INT,[DATE] DATETIME,CHARGE NVARCHAR(200),[MONEY] MONEY)
GO
INSERT #INVOICE VALUES(1,' 2003-05-09 00:00:00',N'材料费', 234.0000)
INSERT #INVOICE VALUES(1,' 2003-05-09 00:00:00',N'上门服务费', 12.0000)
INSERT #INVOICE VALUES(2,'2003-05-09 00:00:00',N'材料费',8.0000)
INSERT #INVOICE VALUES(2,'2003-05-09 00:00:00',N'上门服务费',100.0000)
GO
select no,date,charge,sum(money) AS MONEY from #invoice
group by no,date,charge WITH CUBE
having grouping(no)=0 and grouping(date)=0
GO
DROP TABLE #INVOICE
group by no,date,charge,money,no with rollup
where not(no is null and date is null and charge is null and money )