DECLARE @s1 varchar(100),@s2 varchar(100);SELECT @s1=',2,3,5,7,8,9,10,',@s2=',1,3,6,8,10,'SELECT CONVERT(varchar(100), ( SELECT x+',' AS [text()] FROM( SELECT x FROM( SELECT B.x.value('.','varchar(10)') AS x FROM (SELECT CONVERT(xml,'<v>'+REPLACE(@s1,',','</v><v>')+'</v>') AS x) AS A CROSS APPLY A.x.nodes('//v') AS B(x) ) AS A INTERSECT SELECT x FROM( SELECT B.x.value('.','varchar(10)') AS x FROM (SELECT CONVERT(xml,'<v>'+REPLACE(@s2,',','</v><v>')+'</v>') AS x) AS A CROSS APPLY A.x.nodes('//v') AS B(x) ) AS A ) AS T FOR XML PATH('') ) )
declare @m varchar(100),@n varchar(100) declare @a table(ID int) declare @b table(ID int) select @m='2,3,5,7,8,9,10',@n='1,3,6,8,10' set @m='select '+replace(@m,',',' union select ') set @n='select '+replace(@n,',',' union select ') insert @a exec(@m) insert @b exec(@n) select a.ID from @a a ,@b b where a.id=b.id /* ID ----------- 3 8 10*/
declare @i varchar(10),@s1 varchar(100),@s2 varchar(100),@sr varchar(100) set @s1=',2,3,5,7,8,9,10,' set @s2=',1,3,6,8,10,' set @sr=',' set @i=substring(@s1,1,CHARINDEX(',',@s1)-1) while CHARINDEX(',',@s1)>0 begin if (charindex(@i,@s2)>0) and (@i<>',') set @sr=@sr+@i+',' set @s1=RIGHT(@s1,LEN(@s1)-CHARINDEX(',',@s1)) if CHARINDEX(',',@s1)>0 set @i=substring(@s1,1,CHARINDEX(',',@s1)-1) end select @sr value
select isnull(case when charindex('1,',',2,3,5,7,8,9,10,')*charindex('1,',',1,3,6,8,10,')>0 then ',1'end,'') +isnull(case when charindex('2',',2,3,5,7,8,9,10,')*charindex('2',',1,3,6,8,10,')>0 then ',2'end,'') +isnull(case when charindex('3',',2,3,5,7,8,9,10,')*charindex('3',',1,3,6,8,10,')>0 then ',3'end,'') +isnull(case when charindex('4',',2,3,5,7,8,9,10,')*charindex('4',',1,3,6,8,10,')>0 then ',4'end,'') +isnull(case when charindex('5',',2,3,5,7,8,9,10,')*charindex('5',',1,3,6,8,10,')>0 then ',5'end,'') +isnull(case when charindex('6',',2,3,5,7,8,9,10,')*charindex('6',',1,3,6,8,10,')>0 then ',6'end,'') +isnull(case when charindex('7',',2,3,5,7,8,9,10,')*charindex('7',',1,3,6,8,10,')>0 then ',7'end,'') +isnull(case when charindex('8',',2,3,5,7,8,9,10,')*charindex('8',',1,3,6,8,10,')>0 then ',8'end,'') +isnull(case when charindex('9',',2,3,5,7,8,9,10,')*charindex('9',',1,3,6,8,10,')>0 then ',9'end,'') +isnull(case when charindex('10',',2,3,5,7,8,9,10,')*charindex('10',',1,3,6,8,10,')>0 then ',10'end,'')到10为止,多了就死
-- 一条语句的 (虽然方法不太好 但结果总是对的 呵呵 ) select number from master.dbo.spt_values where type='p' and charindex(','+cast(number as varchar(10))+',',',2,3,5,7,8,9,10,')<>0 and charindex(','+cast(number as varchar(10))+',',',1,3,6,8,10,')<>0;/* number ----------- 3 8 10(3 行受影响) */
DECLARE @s1 varchar(100),@s2 varchar(100);SELECT @s1=',2,3,5,7,8,9,10,',@s2=',1,3,6,8,10,'SELECT CONVERT(varchar(100),
(
SELECT x+',' AS [text()]
FROM(
SELECT x
FROM(
SELECT B.x.value('.','varchar(10)') AS x
FROM (SELECT CONVERT(xml,'<v>'+REPLACE(@s1,',','</v><v>')+'</v>') AS x) AS A
CROSS APPLY A.x.nodes('//v') AS B(x)
) AS A
INTERSECT
SELECT x
FROM(
SELECT B.x.value('.','varchar(10)') AS x
FROM (SELECT CONVERT(xml,'<v>'+REPLACE(@s2,',','</v><v>')+'</v>') AS x) AS A
CROSS APPLY A.x.nodes('//v') AS B(x)
) AS A
) AS T
FOR XML PATH('')
)
)
declare @a table(ID int)
declare @b table(ID int)
select @m='2,3,5,7,8,9,10',@n='1,3,6,8,10'
set @m='select '+replace(@m,',',' union select ')
set @n='select '+replace(@n,',',' union select ')
insert @a exec(@m)
insert @b exec(@n)
select a.ID
from @a a ,@b b
where a.id=b.id
/*
ID
-----------
3
8
10*/
set @s1=',2,3,5,7,8,9,10,'
set @s2=',1,3,6,8,10,'
set @sr=','
set @i=substring(@s1,1,CHARINDEX(',',@s1)-1)
while CHARINDEX(',',@s1)>0
begin
if (charindex(@i,@s2)>0) and (@i<>',')
set @sr=@sr+@i+','
set @s1=RIGHT(@s1,LEN(@s1)-CHARINDEX(',',@s1))
if CHARINDEX(',',@s1)>0
set @i=substring(@s1,1,CHARINDEX(',',@s1)-1)
end
select @sr value
+isnull(case when charindex('2',',2,3,5,7,8,9,10,')*charindex('2',',1,3,6,8,10,')>0 then ',2'end,'')
+isnull(case when charindex('3',',2,3,5,7,8,9,10,')*charindex('3',',1,3,6,8,10,')>0 then ',3'end,'')
+isnull(case when charindex('4',',2,3,5,7,8,9,10,')*charindex('4',',1,3,6,8,10,')>0 then ',4'end,'')
+isnull(case when charindex('5',',2,3,5,7,8,9,10,')*charindex('5',',1,3,6,8,10,')>0 then ',5'end,'')
+isnull(case when charindex('6',',2,3,5,7,8,9,10,')*charindex('6',',1,3,6,8,10,')>0 then ',6'end,'')
+isnull(case when charindex('7',',2,3,5,7,8,9,10,')*charindex('7',',1,3,6,8,10,')>0 then ',7'end,'')
+isnull(case when charindex('8',',2,3,5,7,8,9,10,')*charindex('8',',1,3,6,8,10,')>0 then ',8'end,'')
+isnull(case when charindex('9',',2,3,5,7,8,9,10,')*charindex('9',',1,3,6,8,10,')>0 then ',9'end,'')
+isnull(case when charindex('10',',2,3,5,7,8,9,10,')*charindex('10',',1,3,6,8,10,')>0 then ',10'end,'')到10为止,多了就死
-- 一条语句的 (虽然方法不太好 但结果总是对的 呵呵 )
select number
from master.dbo.spt_values
where type='p'
and charindex(','+cast(number as varchar(10))+',',',2,3,5,7,8,9,10,')<>0
and charindex(','+cast(number as varchar(10))+',',',1,3,6,8,10,')<>0;/*
number
-----------
3
8
10(3 行受影响)
*/