在数据库中存的是一天一天的数据,在SQL2005中有没有这样的函数可以实现本月累计和到目前累计的计算?还是要自己写SQL实现汇总功能?谢谢.如数据库中存的数据:2009-11-30 数量:100
2009-12-01 数量:100
2009-12-02 数量:110
2009-12-03 数量:120
报表中想要查看:
本月累计:330
到目前为止累计总量:430
2009-12-01 数量:100
2009-12-02 数量:110
2009-12-03 数量:120
报表中想要查看:
本月累计:330
到目前为止累计总量:430
insert into tb values('2009-11-30',100 )
insert into tb values('2009-12-01',100 )
insert into tb values('2009-12-02',110 )
insert into tb values('2009-12-03',120 )
goselect dt , sl , ljsl = (select sum(sl) from tb where dt <= t.dt) from tb tdrop table tb/*
dt sl ljsl
------------------------------------------------------ ----------- -----------
2009-11-30 00:00:00.000 100 100
2009-12-01 00:00:00.000 100 200
2009-12-02 00:00:00.000 110 310
2009-12-03 00:00:00.000 120 430(所影响的行数为 4 行)
*/
UNION ALL
SELECT '到目前为止累计总量:',SUM(数量) AS 数量 FROM TB
insert into TB values('2009-11-30',100 )
insert into TB values('2009-12-01',100 )
insert into TB values('2009-12-02',110 )
insert into TB values('2009-12-03',120 )
GOSELECT '本月累计:',SUM(数量) AS 数量 FROM TB WHERE DATEDIFF(MM,[DATE],GETDATE())=0
UNION ALL
SELECT '到目前为止累计总量:',SUM(数量) AS 数量 FROM TB/**
数量
-------------------- -----------
本月累计: 330
到目前为止累计总量: 430(所影响的行数为 2 行)
**/
insert into tb values('2009-11-30',100 )
insert into tb values('2009-12-01',100 )
insert into tb values('2009-12-02',110 )
insert into tb values('2009-12-03',120 )
goselect convert(varchar(10),dt,120) dt ,sl, ljsl = (select sum(sl) from tb where dt <= t.dt) from tb t
union all
select distinct convert(varchar(7),dt,120)+'累计' dt ,sl=0, ljsl = (select sum(sl) from tb where convert(varchar(7),dt,120) <= convert(varchar(7),t.dt,120)) from tb t
order by dt drop table tb/*
dt sl ljsl
----------- ----------- -----------
2009-11-30 100 100
2009-11累计 0 100
2009-12-01 100 200
2009-12-02 110 310
2009-12-03 120 430
2009-12累计 0 430(所影响的行数为 6 行)*/