日期:2014-05-17  浏览次数:20722 次

SQL 分组进行小计
SQL code

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    


要求对日期进行分组统计col2的值

------解决方案--------------------
SQL code

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 @table1
UNION ALL
SELECT NULL, datecol+'小计', SUM(col2)
FROM @table1
GROUP BY datecol
)
SELECT * FROM c1 ORDER BY datecol


id          datecol                  col2
----------- ------------------------ -----------
1           20121001                 10
2           20121001                 20
3           20121001                 10
NULL        20121001小计               40
4           20121002                 30
5           20121002                 10
6           20121002                 10
NULL        20121002小计               50
7           20121003                 20
8           20121003                 10
9           20121003                 10
NULL        20121003小计               40

(12 行受影响)