应该是比较困难的sql 语句, 游标?存储过程?

Question

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

Answer 1

select position,sdate
UNION ALL select position,edate

Answer 2

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 curdeclare @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
endselect * from @Tclose 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)

Answer 3

declare @l_sRQ     char(10)
declare @l_eRQ char(10)
declare @l_tmpRQ char(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_curselect * from #tt