34,590
社区成员
发帖
与我相关
我的任务
分享
--> (让你望见影子的墙)生成测试数据,时间:2008-12-10
if not object_id('tb') is null
drop table tb
Go
Create table tb([名称] nvarchar(3),[日期] Datetime,[贷款余额] int,[利率]varchar(10))
Insert tb
select N'公司A','2007-10-15',100,null union all
select N'公司A','2008-1-1',100,null union all
select N'公司A','2008-1-15',100,null union all
select N'公司B','2008-1-1',200,null union all
select N'公司B','2008-1-2',200,null
Go
Select * from tb
--> (让你望见影子的墙)生成测试数据,时间:2008-12-10
if not object_id('tb2') is null
drop table tb2
Go
Create table tb2([日期] Datetime,[利率] nvarchar(4))
Insert tb2
select '2007-10-15',N'3%' union all
select '2007-1-1',N'3%' union all
select '2008-1-15',N'3.5%' union all
select '2008-1-1',N'2.9%'
Go
Select * from tb2
--> (让你望见影子的墙)生成测试数据,时间:2008-12-10
declare @t table(名称 nvarchar(3),rq datetime)
declare @name varchar(100),@date datetime
declare cur cursor for select 名称, min(日期) from tb group by 名称
open cur
fetch next from cur into @name,@date
while @@fetch_status=0
begin
insert @t values(@name,@date)
while @date<=getdate()
begin
set @date=dateadd(month,3,@date)
insert @t
select @name,@date
end
fetch next from cur into @name,@date
end
close cur
deallocate cur
update tb
set 利率=(select top 1 利率 from (select t.名称,t.rq,tb2.利率 from @t t left join tb2 on t.rq=tb2.日期 )K
where k.rq<=tb.日期 and k.名称=tb.名称 order by k.rq desc)
select * from tb
公司A 2007-10-15 00:00:00.000 100 3%
公司A 2008-01-01 00:00:00.000 100 3%
公司A 2008-01-15 00:00:00.000 100 3.5%
公司B 2008-01-01 00:00:00.000 200 2.9%
公司B 2008-01-02 00:00:00.000 200 2.9%
--> (让你望见影子的墙)生成测试数据,时间:2008-12-10
if not object_id('tb') is null
drop table tb
Go
Create table tb([名称] nvarchar(3),[日期] Datetime,[贷款余额] int,[利率]varchar(10))
Insert tb
select N'公司A','2008-10-15',100,null union all
select N'公司A','2009-1-1',100,null union all
select N'公司A','2009-1-2',100,null union all
select N'公司B','2009-1-1',200,null union all
select N'公司B','2009-1-2',200,null
Go
Select * from tb
--> (让你望见影子的墙)生成测试数据,时间:2008-12-10
if not object_id('tb2') is null
drop table tb2
Go
Create table tb2([日期] Datetime,[利率] nvarchar(4))
Insert tb2
select '2008-10-15',N'3%' union all
select '2009-1-1',N'3%' union all
select '2009-1-15',N'3%' union all
select '2009-4-1',N'2.9%'
Go
Select * from tb2
declare @t table(名称 nvarchar(3),rq datetime)
declare @name varchar(100),@date datetime
declare cur cursor for select 名称, min(日期) from tb group by 名称
open cur
fetch next from cur into @name,@date
while @@fetch_status=0
begin
insert @t values(@name,@date)
while @date<=getdate()
begin
set @date=dateadd(month,3,@date)
insert @t
select @name,@date
end
fetch next from cur into @name,@date
end
close cur
deallocate cur
update tb
set 利率=(select top 1 利率 from (select t.名称,t.rq,tb2.利率 from @t t left join tb2 on t.rq=tb2.日期 )K
where k.rq<=tb.日期 and k.名称=tb.名称 order by k.rq desc)
select * from tb
公司A 2008-10-15 00:00:00.000 2008-10-15 00:00:00.000 3%
公司A 2009-01-15 00:00:00.000 2009-01-15 00:00:00.000 3%
公司B 2009-01-01 00:00:00.000 2009-01-01 00:00:00.000 3%