select * from 表A where name='哈' and xbegin between 1830250 and 1830252
查出一下结果name x 哈 1830250 哈 1830251 哈 1830252
declare @表A table (name varchar(2),xbegin int,xend int) insert into @表A select '哈',1830250,1830252select name,xbegin=a.xbegin+number from @表A a left join (select number from master..spt_values where type='p') b on 1=1 where number <=xend-xbegin /* name xbegin ---- ----------- 哈 1830250 哈 1830251 哈 1830252 */
select name,xbegin=a.xbegin+number from 表A a left join (select number from master..spt_values where type='p') b on 1=1 where number <=xend-xbegin
declare @表A table (name varchar(2),xbegin int,xend int) insert into @表A select '哈',1830250,1830252 select s.name,s.xbegin+number as xbegin from @表A s join master..spt_values on 1=1 where number<=xend-xbegin and type='p'(1 行受影响) name xbegin ---- ----------- 哈 1830250 哈 1830251 哈 1830252(3 行受影响)
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([name] varchar(2),[xbegin] int,[xend] int) insert [tb] select '哈',1830250,1830252 --------------开始查询--------------------------select t.name,t.[xbegin]+s.[number] as [xbegin] from [tb] t,master..spt_values s where t.[xbegin]+s.[number]<=t.[xend] and s.type='p'
select * from 表A where name='哈'
select * from 表A where name='哈' and xbegin between 1830250 and 1830252
哈 1830250
哈 1830251
哈 1830252
declare @表A table (name varchar(2),xbegin int,xend int)
insert into @表A
select '哈',1830250,1830252select name,xbegin=a.xbegin+number
from @表A a left join
(select number from master..spt_values where type='p') b on 1=1
where number <=xend-xbegin
/*
name xbegin
---- -----------
哈 1830250
哈 1830251
哈 1830252
*/
select name,xbegin=a.xbegin+number from 表A a
left join (select number from master..spt_values where type='p') b on 1=1
where number <=xend-xbegin
insert into @表A
select '哈',1830250,1830252
select s.name,s.xbegin+number as xbegin from @表A s
join master..spt_values
on 1=1 where number<=xend-xbegin and type='p'(1 行受影响)
name xbegin
---- -----------
哈 1830250
哈 1830251
哈 1830252(3 行受影响)
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(2),[xbegin] int,[xend] int)
insert [tb]
select '哈',1830250,1830252
--------------开始查询--------------------------select t.name,t.[xbegin]+s.[number] as [xbegin] from [tb] t,master..spt_values s
where t.[xbegin]+s.[number]<=t.[xend] and s.type='p'
----------------结果----------------------------
/*
name xbegin
---- -----------
哈 1830250
哈 1830251
哈 1830252(3 行受影响)*/