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 行受影响)