应该是比较困难的sql 语句, 游标?存储过程?
id position sdate edate
1 a1 2006-12-12 2006-12-18
2 a2 2007-11-18 2007-12-1
想用存储过程或者游标等方式 根据 sdate 和 edate 创建出临时表
position date
a1 2006-12-12
a1 2006-12-13
... ....
a1 2006-12-18
a2 2007-11-18
.... ....
a2 2007-12-1
[解决办法]
create table T(id int, position varchar(10), sdate datetime, edate datetime)
insert T select 1, 'a1 ', '2006-12-12 ', '2006-12-18 '
union all select 2, 'a2 ', '2007-11-18 ', '2007-12-1 '
declare @T table (position varchar(10), [date] datetime)
declare cur cursor for select position, sdate, edate from T
open cur
declare @position varchar(10), @sdate datetime, @edate datetime
fetch next from cur into @position, @sdate, @edate
while @@fetch_status=0
begin
while @sdate <=@edate
begin
insert @T select @position, @sdate
set @sdate=@sdate+1
end
fetch next from cur into @position, @sdate, @edate
end
select * from @T
close cur
deallocate cur
--result
position date
---------- ------------------------------------------------------
a1 2006-12-12 00:00:00.000
a1 2006-12-13 00:00:00.000
a1 2006-12-14 00:00:00.000
a1 2006-12-15 00:00:00.000
a1 2006-12-16 00:00:00.000
a1 2006-12-17 00:00:00.000
a1 2006-12-18 00:00:00.000
a2 2007-11-18 00:00:00.000
a2 2007-11-19 00:00:00.000
a2 2007-11-20 00:00:00.000
a2 2007-11-21 00:00:00.000
a2 2007-11-22 00:00:00.000
a2 2007-11-23 00:00:00.000
a2 2007-11-24 00:00:00.000
a2 2007-11-25 00:00:00.000
a2 2007-11-26 00:00:00.000
a2 2007-11-27 00:00:00.000
a2 2007-11-28 00:00:00.000
a2 2007-11-29 00:00:00.000
a2 2007-11-30 00:00:00.000
a2 2007-12-01 00:00:00.000
(21 row(s) affected)
[解决办法]
declare@l_sRQ char(10)
declare@l_eRQchar(10)
declare@l_tmpRQchar(10)
declare @ID INT
declare @position char(2)
declare cursor test_cur for
select id,position,sdate,edate FROM 表..
OPEN test_cur
create table #tt (position char(2),date char(10))
FETCH NEXT FROM test_cur INTO @ID,@position,@l_sRQ,@l_eRQ
WHILE @@fetch_status=0
BEGIN
SELECT @l_tmpRQ=@l_sRQ
WHILE (@l_tmpRQ <=@l_eRQ)
BEGIN
insert into #tt(position,date)
SELECT @position,@l_tmpRQ
SELECT @l_tmpRQ=convert(char(10),DATEADD(day,1,@l_tmpRQ),102)
END
FETCH NEXT from test_cur into @ID,@position,@l_sRQ,@l_eRQ
END
CLOSE test_cur
select * from #tt
