select
PDAT,
Total =(select sum(PNUM ) from tb b where b.PDAT =a.PDAT ),
CumulateTotal =(select sum(PNUM ) from tb b where b.PDAT <a.PDAT )
from tb a
group by PDAT
PDAT,
Total =(select sum(PNUM ) from tb b where b.PDAT =a.PDAT ),
CumulateTotal =(select sum(PNUM ) from tb b where b.PDAT <a.PDAT )
from tb a
group by PDAT
insert into tb values(300 ,2005 )
insert into tb values(200 ,2005 )
insert into tb values(100 ,2005 )
insert into tb values(300 ,2006 )
insert into tb values(300 ,2006 )
insert into tb values(100 ,2006 )
insert into tb values(400 ,2007 )
insert into tb values(300 ,2007 )
insert into tb values(100 ,2007 )
insert into tb values(400 ,2008 )
insert into tb values(400 ,2008 )
insert into tb values(100 ,2008 )
goselect pdat , sum(pnum) Total , CumulateTotal = (select sum(pnum) from tb where pdat <= t.pdat) from tb t group by pdatdrop table tb/*
pdat Total CumulateTotal
----------- ----------- -------------
2005 600 600
2006 700 1300
2007 800 2100
2008 900 3000(所影响的行数为 4 行)
*/