本月的2日到次月的1日为一个整月,1月2日到次年的1月1日为一个整年,我想循环求出月累和年累数,数据量很大,我只是截取了其中的一小部分,求各位大大帮忙,先谢谢了?id 时间 总购日累 总购月累 总购年累 商品日累 商品月累 商品年累
1 2010-6-9 490472 490170
1 2010-6-10 475110 476061
1 2010-7-2 455993 456705
1 2010-7-30 791037 791285
1 2010-7-31 80232 80289
1 2010-8-21 517457 517638
1 2010-8-22 531709 531920
1 2010-9-1 508646 508913
1 2010-9-2 521942 522069
1 2010-9-30 514238 515272
1 2010-10-1 87104 487590
1 2010-10-3 1626030 626883
1 2010-11-1 524300 524966
1 2010-11-2 31356601 1357108
1 2010-12-1 1477584 1478119
1 2010-12-9 1716936 1716958
1 2010-12-31 685794 1685803
1 2011-1-1 1720004 1720677
1 2011-1-2 1662292 1663076
1 2011-2-14 1664969 1665360
1 2011-2-15 1639400 1641807
1 2010-6-9 490472 490170
1 2010-6-10 475110 476061
1 2010-7-2 455993 456705
1 2010-7-30 791037 791285
1 2010-7-31 80232 80289
1 2010-8-21 517457 517638
1 2010-8-22 531709 531920
1 2010-9-1 508646 508913
1 2010-9-2 521942 522069
1 2010-9-30 514238 515272
1 2010-10-1 87104 487590
1 2010-10-3 1626030 626883
1 2010-11-1 524300 524966
1 2010-11-2 31356601 1357108
1 2010-12-1 1477584 1478119
1 2010-12-9 1716936 1716958
1 2010-12-31 685794 1685803
1 2011-1-1 1720004 1720677
1 2011-1-2 1662292 1663076
1 2011-2-14 1664969 1665360
1 2011-2-15 1639400 1641807
解决方案 »
- 求助:大家都来看看 帮帮忙 急!
- 关于数据库备份概念的困惑
- 就这问题,搞不懂了,呵呵呵,帮下菜鸟我哈
- 取小数点后2位,用什么函数啊
- 一张表中有没有办法设置两个标识列
- 安装SQL Desk Top Engine sp3 出现问题,不知如何是好,请帮忙!
- 打开SQL企业管理嚣出现问题!!!
- 百分相送,怎样滤出重复行
- 怎样在Delphi程序中扑获SQL Server2000中由RAINERROR所产生的异常.
- 请问这个“上一页,下一页,第_页”是怎么实现的
- sql server 2008与sql server 2008 r2的区别?
- 问一个其他人问了好几年的问题:sql server 8企业版能够安装到win7上吗?
create table ta(id int, dt datetime, zgrl int, sprl int);
insert into ta(id, dt, zgrl, sprl)
select '1','2010-6-9','490472','490170' union all
select '1','2010-6-10','475110','476061' union all
select '1','2010-7-2','455993','456705' union all
select '1','2010-7-30','791037','791285' union all
select '1','2010-7-31','80232','80289' union all
select '1','2010-8-21','517457','517638' union all
select '1','2010-8-22','531709','531920' union all
select '1','2010-9-1','508646','508913' union all
select '1','2010-9-2','521942','522069' union all
select '1','2010-9-30','514238','515272' union all
select '1','2010-10-1','87104','487590' union all
select '1','2010-10-3','1626030','626883' union all
select '1','2010-11-1','524300','524966' union all
select '1','2010-11-2','31356601','1357108' union all
select '1','2010-12-1','1477584','1478119' union all
select '1','2010-12-9','1716936','1716958' union all
select '1','2010-12-31','685794','1685803' union all
select '1','2011-1-1','1720004','1720677' union all
select '1','2011-1-2','1662292','1663076' union all
select '1','2011-2-14','1664969','1665360' union all
select '1','2011-2-15','1639400','1641807'-- 月累
select ydt, sumzgrl=sum(zgrl), sumsprl=sum(sprl) from(
select
ydt= case when day(dt)>1 then rtrim(year(dt))+'年'+rtrim(month(dt))+'月'
else rtrim(year(dateadd(m,-1, dt)))+'年'+rtrim(month(dateadd(m,-1, dt)))+'月' end -- day(dt)=1
, *
from ta
) a group by ydt -- 年累 1月2日到次年的1月1日为一个整年
select ndt, sumzgrl=sum(zgrl), sumsprl=sum(sprl) from(
select
ndt= case when datepart(dy, dt)>1 then rtrim(year(dt))+'年'
else rtrim(year(dt)-1)+'年' end -- day(dt)=1
, *
from ta
) a group by ndt
--> 测试数据:[tbl]
go
if object_id('[tbl]') is not null
drop table [tbl]
go
create table [tbl](
[name] varchar(1),
[date] varchar(5),
[num] int
)
go
insert [tbl]
select 'a','1-1号',1 union all
select 'b','1-2号',4 union all
select 'a','1-3号',8 union all
select 'a','1-4号',5 union all
select 'b','1-5号',6 union all
select 'b','1-6号',9;with t
as(
select ROW_NUMBER()over(partition by name
order by [date]) as id,
*,num as total from tbl
),
m as(
select id,name,[date],num,total from t where id=1
union all
select a.id,a.name,a.[date],a.num,b.total+a.num from t a
inner join m b on a.id=b.id+1 and a.name=b.name
)
select name,[date],num,total from m order by name/*
name date num total
a 1-3号 8 8
a 1-4号 5 13
a 1-1号 1 14
b 1-2号 4 4
b 1-5号 6 10
b 1-6号 9 19
*/
-------------------------------------------------------
--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([ID] int,[借方] int,[贷方] int)
insert [tbl]
select 1,10,0 union all
select 2,0,4 union all
select 3,0,2 union all
select 4,1,0SELECT
ID,借方,贷方,
[余额]=(SELECT SUM(借方-贷方) FROM tbl WHERE ID<=a.ID)
FROM tbl AS a分别用日期,年份和月份,年份分组排序,然后找上面给的资料区更改递归
create table t1
(
id int,
riqi datetime,
rileiji int,
yueleiji int,
nianleiji int
)
insert into t1
select 1, '2010-6-9', 490472, 0, 0 union all
select 1, '2010-6-10', 475110, 0, 0 union all
select 1, '2010-7-2', 455993, 0, 0 union all
select 1, '2010-7-30', 791037, 0, 0 union all
select 1, '2010-7-31', 802321, 0, 0 union all
select 1, '2010-8-21', 517457, 0, 0 union all
select 1, '2010-8-22', 531709, 0, 0 union all
select 1, '2010-9-1', 508646, 0, 0 union all
select 1, '2010-9-2', 521942, 0, 0 union all
select 1, '2010-9-30', 514238, 0, 0 union all
select 1, '2010-10-1', 487104, 0, 0 union all
select 1, '2010-10-31', 626030, 0, 0 union all
select 1, '2010-11-1', 524300, 0, 0 union all
select 1, '2010-11-23', 1356601, 0, 0 union all
select 1, '2010-12-5', 1477584, 0, 0 union all
select 1, '2010-12-25', 1716936, 0, 0 union all
select 1, '2010-12-31', 1685794, 0, 0 union all
select 1, '2011-1-1', 1720004, 0, 0 union all
select 1, '2011-1-2', 1662292, 0, 0 union all
select 1, '2011-2-14', 1664969, 0, 0 union all
select 1, '2011-2-15', 1639400, 0, 0
select * from t1 select a.id,a.riqi,a.rileiji,
case when DAY(a.riqi)=1
then (select SUM(rileiji) from t1 where riqi>=convert(varchar(7),DATEADD(MONTH,-1,a.riqi),120)+'-02'
and riqi<=a.riqi)
else (select SUM(rileiji) from t1 where riqi>=convert(varchar(7),a.riqi,120)+'-02'
and riqi<=a.riqi)
end as yueleiji,
case when MONTH(a.riqi)=1 and DAY(a.riqi)=1
then (select SUM(rileiji) from t1 where riqi>=convert(varchar(7),DATEADD(YEAR,-1,a.riqi),120)+'-01'
and riqi<=a.riqi)
else (select SUM(rileiji) from t1 where riqi>=CONVERT(varchar(5),a.riqi,120)+'01-02'
and riqi<=a.riqi)
end as nianleiji
from t1 as a
----------------------------
id riqi rileiji yueleiji nianleiji
1 2010-06-09 00:00:00.000 490472 490472 490472
1 2010-06-10 00:00:00.000 475110 965582 965582
1 2010-07-02 00:00:00.000 455993 455993 1421575
1 2010-07-30 00:00:00.000 791037 1247030 2212612
1 2010-07-31 00:00:00.000 802321 2049351 3014933
1 2010-08-21 00:00:00.000 517457 517457 3532390
1 2010-08-22 00:00:00.000 531709 1049166 4064099
1 2010-09-01 00:00:00.000 508646 1557812 4572745
1 2010-09-02 00:00:00.000 521942 521942 5094687
1 2010-09-30 00:00:00.000 514238 1036180 5608925
1 2010-10-01 00:00:00.000 487104 1523284 6096029
1 2010-10-31 00:00:00.000 626030 626030 6722059
1 2010-11-01 00:00:00.000 524300 1150330 7246359
1 2010-11-23 00:00:00.000 1356601 1356601 8602960
1 2010-12-05 00:00:00.000 1477584 1477584 10080544
1 2010-12-25 00:00:00.000 1716936 3194520 11797480
1 2010-12-31 00:00:00.000 1685794 4880314 13483274
1 2011-01-01 00:00:00.000 1720004 6600318 15203278
1 2011-01-02 00:00:00.000 1662292 1662292 1662292
1 2011-02-14 00:00:00.000 1664969 1664969 3327261
1 2011-02-15 00:00:00.000 1639400 3304369 4966661