如何循环求值?
本月的2日到次月的1日为一个整月,1月2日到次年的1月1日为一个整年,我想循环求出月累和年累数,数据量很大,我只是截取了其中的一小部分,求各位大大帮忙,先谢谢了?
id时间总购日累总购月累总购年累商品日累商品月累商品年累
12010-6-9 490472490170
12010-6-10 475110476061
12010-7-2 455993456705
12010-7-30 791037791285
12010-7-31 8023280289
12010-8-21 517457517638
12010-8-22 531709531920
12010-9-1 508646508913
12010-9-2 521942522069
12010-9-30 514238515272
12010-10-1 87104 487590
12010-10-3 1626030626883
12010-11-1 524300524966
12010-11-2 313566011357108
12010-12-1 14775841478119
12010-12-9 17169361716958
12010-12-31 6857941685803
12011-1-1 17200041720677
12011-1-2 16622921663076
12011-2-14 16649691665360
12011-2-15 16394001641807
[解决办法]
--> 测试数据:[tbl]goif object_id('[tbl]') is not null drop table [tbl]gocreate table [tbl]([name] varchar(1),[date] varchar(5),[num] int)goinsert [tbl]select 'a','1-1号',1 union allselect 'b','1-2号',4 union allselect 'a','1-3号',8 union allselect 'a','1-4号',5 union allselect 'b','1-5号',6 union allselect 'b','1-6号',9;with tas(select ROW_NUMBER()over(partition by nameorder by [date]) as id,*,num as total from tbl),m as(select id,name,[date],num,total from t where id=1union allselect a.id,a.name,a.[date],a.num,b.total+a.num from t ainner 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 totala 1-3号 8 8a 1-4号 5 13a 1-1号 1 14b 1-2号 4 4b 1-5号 6 10b 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 allselect 2,0,4 union allselect 3,0,2 union allselect 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 t1select 1, '2010-6-9', 490472, 0, 0 union allselect 1, '2010-6-10', 475110, 0, 0 union allselect 1, '2010-7-2', 455993, 0, 0 union allselect 1, '2010-7-30', 791037, 0, 0 union allselect 1, '2010-7-31', 802321, 0, 0 union allselect 1, '2010-8-21', 517457, 0, 0 union allselect 1, '2010-8-22', 531709, 0, 0 union allselect 1, '2010-9-1', 508646, 0, 0 union allselect 1, '2010-9-2', 521942, 0, 0 union allselect 1, '2010-9-30', 514238, 0, 0 union allselect 1, '2010-10-1', 487104, 0, 0 union allselect 1, '2010-10-31', 626030, 0, 0 union allselect 1, '2010-11-1', 524300, 0, 0 union allselect 1, '2010-11-23', 1356601, 0, 0 union allselect 1, '2010-12-5', 1477584, 0, 0 union allselect 1, '2010-12-25', 1716936, 0, 0 union allselect 1, '2010-12-31', 1685794, 0, 0 union allselect 1, '2011-1-1', 1720004, 0, 0 union allselect 1, '2011-1-2', 1662292, 0, 0 union allselect 1, '2011-2-14', 1664969, 0, 0 union allselect 1, '2011-2-15', 1639400, 0, 0select * 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 nianleijifrom t1 as a