create table jied(no char(10),name char(10),dat int)
insert into jied
select 'p1','one',1 union
select 'p2','two',8 union
select 'p3','three',20
--需求结果
p1 one 1
p1 one 2
p1 one 3
p1 one 4
p1 one 5
p1 one 6
p1 one 7
p2 two 8
p2 two 9
p2 two 10
p2 two 11
p2 two 12
p2 two 13
p2 two 14
p2 two 15
p2 two 16
p2 two 17
p2 two 18
p2 two 19
p3 three 20
------解决方案--------------------
MASTER..SPT_VALUES表构造数据。
------解决方案--------------------
- SQL code
SELECT no,A.name,numberFROM jied AS A, MASTER..SPT_VALUES AS BWHERE B.type = 'P' AND DAT <= number AND number < (SELECT CASE WHEN MIN(DAT) IS NULL THEN (SELECT MAX(DAT) + 1 FROM jied) ELSE MIN(DAT) END FROM jied AS C WHERE A.dat < C.dat)no name numberp1 one 1p1 one 2p1 one 3p1 one 4p1 one 5p1 one 6p1 one 7p2 two 8p2 two 9p2 two 10p2 two 11p2 two 12p2 two 13p2 two 14p2 two 15p2 two 16p2 two 17p2 two 18p2 two 19p3 three 20
------解决方案--------------------
CREATE table xl (
id int null
) ON [PRIMARY]
insert into xl
select number AS N
from master..spt_values
where type='p' and number between 1 and 100
drop table jied
create table jied(no char(10),name char(10),dat int,dat1 int)
insert into jied
select 'p1','one',1,8 union
select 'p2','two',8,20 union
select 'p3','three',20,21
select no,name,id from (
select no,name,dat,dat1 from jied )a
left join
xl b on b.id>=dat and b.id<dat1
我这有个笨办法