create table t2(c1 varchar(15),c2 varchar(15)) insert t2 select '111','222' insert t2 select '333','666' select * from t2select * from t1 a,t2 b where a.item between b.c1 and b.c2/* item c1 c2 --------------- --------------- --------------- 111 111 222 222 111 222 333 333 666 444 333 666 555 333 666 666 333 666(6 行受影响) */
select * into #aa from t2 with(nolock) declare @c1 varchar(15) declare @c2 varchar(15) declare @sqlStr varchar(4000) set @sqlStr='' while exists (select top(1)1 from #aa with(nolock)) begin select top(1) @c1=c1,@c2=c2 from t2 with(nolock) set @sqlStr=@sqlStr+'select * from t1 where item'>@c1 +'and item'<=@c2+' union all' delete #aa where c1=@c1 and c2=@c2 end set @sqlStr=substring(@sqlStr,1,len(@sqlStr)-9) exec(@sqlStr)
select a.* from t1 a,t2 b where a.item > b.c1 and a.item<=b.c2
select * into #aa from t2 with(nolock) declare @c1 varchar(15) declare @c2 varchar(15) declare @sqlStr varchar(4000) set @sqlStr='' while exists (select top(1)1 from #aa with(nolock)) begin select top(1) @c1=c1,@c2=c2 from #aa with(nolock) set @sqlStr=@sqlStr+' select * from t1 where item>'+''''+@c1+''''+' and item<='+''''+@c2+''''+' union all' delete #aa where c1=@c1 and c2=@c2 end set @sqlStr=substring(@sqlStr,1,len(@sqlStr)-9) select @sqlStr exec(@sqlStr)--结果---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select * from t1 where item>'111' and item<='222' union all select * from t1 where item>'333' and item<='666' (1 行受影响)item --------------- 222 444 555 666(4 行受影响)
create table t1(item varchar(15))
insert t1 select '111'
insert t1 select '222'
insert t1 select '333'
insert t1 select '444'
insert t1 select '555'
insert t1 select '666'
insert t1 select '777'
insert t1 select '888'
insert t1 select '999'
select * from t1
create table t2(c1 varchar(15),c2 varchar(15))
insert t2 select '111','222'
insert t2 select '333','666'
select * from t2select * from t1 a,t2 b where a.item between b.c1 and b.c2/*
item c1 c2
--------------- --------------- ---------------
111 111 222
222 111 222
333 333 666
444 333 666
555 333 666
666 333 666(6 行受影响)
*/
select * into #aa from t2 with(nolock)
declare @c1 varchar(15)
declare @c2 varchar(15)
declare @sqlStr varchar(4000)
set @sqlStr=''
while exists (select top(1)1 from #aa with(nolock))
begin
select top(1) @c1=c1,@c2=c2 from t2 with(nolock)
set @sqlStr=@sqlStr+'select * from t1 where item'>@c1 +'and item'<=@c2+' union all'
delete #aa where c1=@c1 and c2=@c2
end
set @sqlStr=substring(@sqlStr,1,len(@sqlStr)-9)
exec(@sqlStr)
select * into #aa from t2 with(nolock)
declare @c1 varchar(15)
declare @c2 varchar(15)
declare @sqlStr varchar(4000)
set @sqlStr=''
while exists (select top(1)1 from #aa with(nolock))
begin
select top(1) @c1=c1,@c2=c2 from #aa with(nolock)
set @sqlStr=@sqlStr+' select * from t1 where item>'+''''+@c1+''''+' and item<='+''''+@c2+''''+' union all'
delete #aa where c1=@c1 and c2=@c2
end
set @sqlStr=substring(@sqlStr,1,len(@sqlStr)-9)
select @sqlStr
exec(@sqlStr)--结果----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from t1 where item>'111' and item<='222' union all select * from t1 where item>'333' and item<='666' (1 行受影响)item
---------------
222
444
555
666(4 行受影响)