你可以用一个变量来存放呀 DECLARE @VAR VARCHAR(8000) SET @VAR=',pk1','pk2',.....,'pk1005,' SELECT * FROM TABLEA WHERE CHARINDEX(','+pk_tablea+',',@VAR)>0
select * from tablea INNER JOIN ( select 'pk1' as 'pk_tablea' union all select 'pk2' ... union all select 'pk1005' ) T ON TABLEA.pk_tablea=T.pk_tablea
建一张临时表,把 in() 后面的数据都放在临时表里,然后 select * from tablea t where exists(select 1 from # where pk_tablea=t.pk_tablea)
用这个试试 select * from tablea where pk_tablea in ( select 'pk'+cast(number as varchar(12)) from spt_values where number between 1 and 1005 and type='p' )
declare @t table(pk varchar(50))insert into @t select 'pk1' union all select 'pk2' union all select 'pk3' union all select 'pk4' union all select 'pk5' select * from tablea t where exists (select 1 from @t where pk_tablea=t.pk)其实这种改成 select * from tablea t1 join @t t2 on t1. pk_tablea=t2.pk还快一些
declare @tablea table(CID int, pk_tablea varchar(12)) insert @tablea select 2 , 'pk1' union all select 3 , 'pk2' union all select 4 , 'pk11' union all select 5 , 'pk11111' union all select 6 , 'pk112' union all select 7 , 'pk1212' union all select 8 , 'pk3' union all select 9 , 'pk4' union all select 10 , 'pk5' union all select 11 , 'pk6' union all select 12 , 'pk7' union all select 13 , '小明' select * from @tablea where pk_tablea in ( select 'pk'+cast(number as varchar(12)) from [master].[dbo].[spt_values] where number between 1 and 1005 and type='p' )CID pk_tablea ----------- ------------ 2 pk1 3 pk2 4 pk11 6 pk112 8 pk3 9 pk4 10 pk5 11 pk6 12 pk7(9 行受影响)
将in里面的值放在一个临时表中,再exists
DECLARE @VAR VARCHAR(8000)
SET @VAR=',pk1','pk2',.....,'pk1005,'
SELECT * FROM TABLEA WHERE CHARINDEX(','+pk_tablea+',',@VAR)>0
INNER JOIN (
select 'pk1' as 'pk_tablea'
union all select 'pk2'
...
union all select 'pk1005'
) T ON TABLEA.pk_tablea=T.pk_tablea
用这个试试
select * from tablea where pk_tablea in (
select 'pk'+cast(number as varchar(12)) from spt_values where number between 1 and 1005 and type='p'
)
select 'pk1' union all
select 'pk2' union all
select 'pk3' union all
select 'pk4' union all
select 'pk5'
select * from tablea t where exists (select 1 from @t where pk_tablea=t.pk)其实这种改成 select * from tablea t1 join @t t2 on t1. pk_tablea=t2.pk还快一些
declare @tablea table(CID int, pk_tablea varchar(12))
insert @tablea select
2 , 'pk1' union all select
3 , 'pk2' union all select
4 , 'pk11' union all select
5 , 'pk11111' union all select
6 , 'pk112' union all select
7 , 'pk1212' union all select
8 , 'pk3' union all select
9 , 'pk4' union all select
10 , 'pk5' union all select
11 , 'pk6' union all select
12 , 'pk7' union all select
13 , '小明' select * from @tablea where pk_tablea in (
select 'pk'+cast(number as varchar(12)) from [master].[dbo].[spt_values] where number between 1 and 1005 and type='p'
)CID pk_tablea
----------- ------------
2 pk1
3 pk2
4 pk11
6 pk112
8 pk3
9 pk4
10 pk5
11 pk6
12 pk7(9 行受影响)
不想建表的话参考我7L的语句,不过原理是一样的。