# 怎么循环求值

www.MyException.Cn  网友分享于：2013-05-13  浏览：11次

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

------解决方案--------------------
SQL code
```
--> 测试数据：[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,0

SELECT
ID,借方,贷方,
[余额]=(SELECT SUM(借方-贷方) FROM tbl WHERE ID<=a.ID)
FROM tbl AS a

------解决方案--------------------
SQL code
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```