你这个逻辑我咋看着有些头晕啊 如果仅仅是select * from TableA X1 join TableB x2 on X1.a=x2.b1 join TableB x3 on X2.b2 =x3.b1 join TableB x4 on X3.b2 =x4.b1这个的话,可以改进如下: select * from TAbleA X1 join TableB X2 on X1.a=X2.b1 where X2.b1=X2.b2 但是问题是你的TableC是怎么回事呢? 莫非你想要下面的结果: select * from TAbleA X1 join TableB X2 on X1.a=X2.b1 where X2.b1=X2.b2 and X2.b2 in (select * from TableC) ? 自己测试一下上面的两个逻辑吧。 感觉你描述的有些乱。
declare @s varchar(8000)='', @i int=7 --表数量 set @i=@i+3 select @s=@s+' join tableb x'+cast(number as varchar(10))+ ' on x'+cast(number-1 as varchar(10))+'.b'+cast(number-1 as varchar(10))+'=x'+cast(number as varchar(10))+'.b1'from master..spt_values where type='p' and number>2 and number<@i select @s='select * from TableA X1 join TableB x2 on X1.a=x2.b1 '+@s exec(@s)
表数量搞错了,重新贴了一个,不知道是不是漏主要的!! declare @s varchar(8000)='', @i int=7 --表数量 set @i=@i+1 select @s=@s+' join tableb x'+cast(number as varchar(10))+ ' on x'+cast(number-1 as varchar(10))+'.b'+cast(number-1 as varchar(10))+'=x'+cast(number as varchar(10))+'.b1'from master..spt_values where type='p' and number>2 and number<@i select @s='select * from TableA X1 join TableB x2 on X1.a=x2.b1 '+@s exec(@s)
用动态SQL就可以解决你说的问题declare @sql varchar(max) set @sql = '……' exec(@sql)拼凑sql的时候你根据条件拼凑就可以了。
如果仅仅是select * from TableA X1
join TableB x2 on X1.a=x2.b1
join TableB x3 on X2.b2 =x3.b1
join TableB x4 on X3.b2 =x4.b1这个的话,可以改进如下:
select * from TAbleA X1 join TableB X2 on X1.a=X2.b1 where X2.b1=X2.b2
但是问题是你的TableC是怎么回事呢?
莫非你想要下面的结果:
select * from TAbleA X1 join TableB X2 on X1.a=X2.b1 where X2.b1=X2.b2 and X2.b2 in (select * from TableC) ?
自己测试一下上面的两个逻辑吧。
感觉你描述的有些乱。
@i int=7 --表数量
set @i=@i+3
select @s=@s+' join tableb x'+cast(number as varchar(10))+ ' on x'+cast(number-1 as varchar(10))+'.b'+cast(number-1 as varchar(10))+'=x'+cast(number as varchar(10))+'.b1'from master..spt_values where type='p' and number>2 and number<@i
select @s='select * from TableA X1 join TableB x2 on X1.a=x2.b1 '+@s
exec(@s)
declare @s varchar(8000)='',
@i int=7 --表数量
set @i=@i+1
select @s=@s+' join tableb x'+cast(number as varchar(10))+ ' on x'+cast(number-1 as varchar(10))+'.b'+cast(number-1 as varchar(10))+'=x'+cast(number as varchar(10))+'.b1'from master..spt_values where type='p' and number>2 and number<@i
select @s='select * from TableA X1 join TableB x2 on X1.a=x2.b1 '+@s
exec(@s)
set @sql = '……'
exec(@sql)拼凑sql的时候你根据条件拼凑就可以了。