如果楼主要这样效果:go create view test asselect [nianfen]=dateadd(yy,t2.ID-1,t1.sj) from (select sj= '1998-05-06',sj2= '2002-01-04')t1 , sysobjects t2 where datediff(yy,t1.sj,t1.sj2)!<t2.ID-1go select * from testnianfen ------------------------------------------------------ 1998-05-06 00:00:00.000 1999-05-06 00:00:00.000 2000-05-06 00:00:00.000 2001-05-06 00:00:00.000(所影响的行数为 4 行)
create view test asselect [nianfen]=year(dateadd(yy,t2.ID-1,t1.sj))--改为显示年份 from (select sj= '1998-05-06',sj2= '2002-01-04')t1 , sysobjects t2 where datediff(yy,t1.sj,t1.sj2)!<t2.ID-1go select * from test nianfen ----------- 1998 1999 2000 2001(所影响的行数为 4 行)
create proc test( @sj datetime,--'1998-05-06' @sj2 datetime--'2002-01-04' ) asdeclare @ta table(nianfen int) while year(@sj)!>year(@sj2) begin insert @ta select year(@sj) set @sj=dateadd(yy,1,@sj) end select * from @ta goexec test '1998-05-06','2002-01-04'nianfen ----------- 1998 1999 2000 2001 2002(所影响的行数为 5 行)
视图改一下: create view test asselect [nianfen]=dateadd(yy,t2.ID-1,t1.sj) from (select sj= '1998-05-06',sj2= '2002-01-04')t1 , sysobjects t2 where datediff(yy,t1.sj,t1.sj2)!<t2.ID-1 union all select '2002-01-04' --添加记录
视图不可以传参: 漏了一个year: create view test asselect [nianfen]=dateadd(yy,t2.ID-1,t1.sj) from (select sj= '1998-05-06',sj2= '2002-01-04')t1 , sysobjects t2 where datediff(yy,t1.sj,t1.sj2)!<t2.ID-1 union all select year('2002-01-04') --添加记录
一个一个select
create view test
asselect [nianfen]=dateadd(yy,t2.ID-1,t1.sj)
from
(select sj= '1998-05-06',sj2= '2002-01-04')t1
,
sysobjects t2
where
datediff(yy,t1.sj,t1.sj2)!<t2.ID-1go
select * from testnianfen
------------------------------------------------------
1998-05-06 00:00:00.000
1999-05-06 00:00:00.000
2000-05-06 00:00:00.000
2001-05-06 00:00:00.000(所影响的行数为 4 行)
asselect [nianfen]=year(dateadd(yy,t2.ID-1,t1.sj))--改为显示年份
from
(select sj= '1998-05-06',sj2= '2002-01-04')t1
,
sysobjects t2
where
datediff(yy,t1.sj,t1.sj2)!<t2.ID-1go
select * from test
nianfen
-----------
1998
1999
2000
2001(所影响的行数为 4 行)
create proc test(
@sj datetime,--'1998-05-06'
@sj2 datetime--'2002-01-04'
)
asdeclare @ta table(nianfen int)
while year(@sj)!>year(@sj2)
begin
insert @ta select year(@sj)
set @sj=dateadd(yy,1,@sj)
end
select * from @ta
goexec test '1998-05-06','2002-01-04'nianfen
-----------
1998
1999
2000
2001
2002(所影响的行数为 5 行)
create view test
asselect [nianfen]=dateadd(yy,t2.ID-1,t1.sj)
from
(select sj= '1998-05-06',sj2= '2002-01-04')t1
,
sysobjects t2
where
datediff(yy,t1.sj,t1.sj2)!<t2.ID-1
union all
select '2002-01-04' --添加记录
漏了一个year:
create view test
asselect [nianfen]=dateadd(yy,t2.ID-1,t1.sj)
from
(select sj= '1998-05-06',sj2= '2002-01-04')t1
,
sysobjects t2
where
datediff(yy,t1.sj,t1.sj2)!<t2.ID-1
union all
select year('2002-01-04') --添加记录