Create Table test1 (
A int not null ,
B char(10) not null,
begindate datetime not null,
enddate datetime not null,
c int not null)insert into test1
select 1,'a','2010-06-02','2010-06-04',5 union all
select 2,'a','2010-06-09','2010-06-12',2 union all
select 3,'b','2010-06-03','2010-06-04',4select * from test11 a 2010-06-02 00:00:00.000 2010-06-04 00:00:00.000 5
2 a 2010-06-09 00:00:00.000 2010-06-12 00:00:00.000 2
3 b 2010-06-03 00:00:00.000 2010-06-04 00:00:00.000 4问题是我想查询出如下结果(我是想作一个视图,有三列,分别是b,datein,c )列出如下:
a 2010-06-02 5
a 2010-06-03 5
a 2010-06-04 5
a 2010-06-09 2
a 2010-06-10 2
a 2010-06-11 2
a 2010-06-12 2
b 2010-06-03 4
b 2010-06-04 4我用的是SQL2000,请问能否实现上述功能?
A int not null ,
B char(10) not null,
begindate datetime not null,
enddate datetime not null,
c int not null)insert into test1
select 1,'a','2010-06-02','2010-06-04',5 union all
select 2,'a','2010-06-09','2010-06-12',2 union all
select 3,'b','2010-06-03','2010-06-04',4
SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO # FROM syscolumns a,syscolumns b,syscolumns cselect
*
from test1 a,# b
WHERE a.begindate+b.ID<=enddate
from test1 t,master..spt_values n
where n.type='p' and n.number<=datediff(d,begindate,enddate)/*
A c
----------- ------------------------------------------------------ -----------
1 2010-06-02 00:00:00.000 5
1 2010-06-03 00:00:00.000 5
1 2010-06-04 00:00:00.000 5
2 2010-06-09 00:00:00.000 2
2 2010-06-10 00:00:00.000 2
2 2010-06-11 00:00:00.000 2
2 2010-06-12 00:00:00.000 2
3 2010-06-03 00:00:00.000 4
3 2010-06-04 00:00:00.000 4(所影响的行数为 9 行)
*/
from test1 t,master..spt_values n
where n.type='p' and n.number<=datediff(d,begindate,enddate)/*
B date c
---------- ---------- -----------
a 2010-06-02 5
a 2010-06-03 5
a 2010-06-04 5
a 2010-06-09 2
a 2010-06-10 2
a 2010-06-11 2
a 2010-06-12 2
b 2010-06-03 4
b 2010-06-04 4(所影响的行数为 9 行)*/
(
A int not null ,
B char(10) not null,
date1 datetime not null,
c int not null
)declare aaa cursor
for select * from test1
open aaa
declare @a varchar(20), @b varchar(20), @c varchar(20), @d varchar(20),@e varchar(20),@date varchar(20)
fetch next from aaa into @a,@b,@c,@d,@e
while @@fetch_status=0
begin
declare @i int
select @i=0,@date='1900-1-1'
while @date<@d
begin
set @date=dateadd(day,@i,@c)
insert into test2 values(@a,@b,@date,@e)
set @i=@i+1
end
fetch next from aaa into @a,@b,@c,@d,@e
end
close aaa
deallocate aaaselect * from test2
A int not null ,
B char(10) not null,
begindate datetime not null,
enddate datetime not null,
c int not null)insert into test1
select 1,'a','2010-06-02','2010-06-04',5 union all
select 2,'a','2010-06-09','2010-06-12',2 union all
select 3,'b','2010-06-03','2010-06-04',4SELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO # FROM syscolumns a, syscolumns b select m.a , m.b , dateadd(dd,n.id,m.begindate) begindate,m.c from test1 m, # n
where dateadd(dd,n.id,m.begindate) <= m.enddatedrop table test1,#/*
a b begindate c
----------- ---------- ------------------------------------------------------ -----------
1 a 2010-06-02 00:00:00.000 5
1 a 2010-06-03 00:00:00.000 5
1 a 2010-06-04 00:00:00.000 5
2 a 2010-06-09 00:00:00.000 2
2 a 2010-06-10 00:00:00.000 2
2 a 2010-06-11 00:00:00.000 2
2 a 2010-06-12 00:00:00.000 2
3 b 2010-06-03 00:00:00.000 4
3 b 2010-06-04 00:00:00.000 4(所影响的行数为 9 行)
*/