一条SQL语句的月总帐

create table temptb (mon int,value int)

insert temptb values (1,20)

insert temptb values (2,20)

insert temptb values (3,20)

insert temptb values (4,20)

insert temptb values (5,20)

insert temptb values (6,20)

insert temptb values (7,20)

insert temptb values (8,20)

insert temptb values (9,20)

insert temptb values (10,20)

insert temptb values (11,20)

insert temptb values (12,20)

declare @month int

set @month=1

while(@month<13)

begin

select sum(value) as value from temptb where mon<=@month

set @month=@month+1

end

go

select * from

(

select mon,value,‘月计‘ as summary from (select mon,sum(value) value from temptb group by mon) as temptb

union all

(select t2.mon,sum(t1.value) as value,‘累计‘ as summary from (select mon,sum(value) value from temptb group by mon) as t1 inner join (select mon,sum(value) value from temptb group by mon) as t2 on t1.mon>=t2.mon

group by t2.mon)

) a order by a.mon asc,a.value asc,a.summary desc

 

select * from temptb