SQL 分组进行小计
declare @table1 table( id int ,datecol varchar(255) ,col2 int)insert into @table1 select 1,'20121001' ,10 union select 2,'20121001',20 union select 3,'20121001',10 union select 4,'20121002',30 union select 5,'20121002',10 union select 6,'20121002',10 union select 7,'20121003',20 union select 8,'20121003',10 union select 9,'20121003',10 --要求对记录按照时间分组统计小计--最后想得到的结果--'20121001' , 20 --'20121001' , 30 --'20121001' , 10 --'小计',60--'20121002' , 30 --'20121002' , 10 --'20121002' , 10 --'小计',50--'20121003' , 20 --'20121003' , 10 --'20121003' , 10 --'小计',40
declare @table1 table( id int ,datecol varchar(20) ,col2 int)insert into @table1 select 1,'20121001' ,10 union select 2,'20121001',20 union select 3,'20121001',10 union select 4,'20121002',30 union select 5,'20121002',10 union select 6,'20121002',10 union select 7,'20121003',20 union select 8,'20121003',10 union select 9,'20121003',10;WITH c1(id, datecol, col2) AS(SELECT *FROM @table1UNION ALLSELECT NULL, datecol+'小计', SUM(col2)FROM @table1GROUP BY datecol)SELECT * FROM c1 ORDER BY datecolid datecol col2----------- ------------------------ -----------1 20121001 102 20121001 203 20121001 10NULL 20121001小计 404 20121002 305 20121002 106 20121002 10NULL 20121002小计 507 20121003 208 20121003 109 20121003 10NULL 20121003小计 40(12 行受影响)
