SELECT no,A.name,number
FROM jied AS A, MASTER..SPT_VALUES AS B
WHERE 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    number
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

------解决方案--------------------
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

我这有个笨办法