DECLARE @a table(a int,b INT) insert @a select 1, 1 union all select 1, 2 union all select 1, 4union all select 2, 2 union all select 2, 4union all select 3, 1 union all select 3, 2 SELECT distinct a FROM @a a WHERE b in(1,2) AND exists(SELECT 1 FROM @a WHERE a=a.a AND b<>a.b AND b in(1,2) )
--result /* a ----------- 1 3(所影响的行数为 2 行)*/
create table TT ( ID int, 类型 int ) insert into TT select 1,1 insert into TT select 1,2 insert into TT select 1,4 insert into TT select 2,2 insert into TT select 2,4 insert into TT select 3,1 insert into TT select 3,2declare @sql varchar(50) select ID,@sql=@sql+','+ltrim(类型) from #TTcreate function dbo.Getinfo(@ID int) returns nvarchar(1000) as begin declare @sql nvarchar(1000) set @sql='' select @sql=@sql+','+ltrim(类型) from TT where ID=@ID return stuff(@sql,1,1,'') enddeclare @str nvarchar(20) set @str='1,2' select distinct ID from ( select distinct ID,dbo.Getinfo(ID) 类型 from TT ) tt where charindex(@str,类型)>0ID ----------- 1 3(2 行受影响)
create table #tt(ID int,类型 int) insert #tt select 1, 1 insert #tt select 1, 2 insert #tt select 1, 4insert #tt select 2, 2 insert #tt select 2, 4insert #tt select 3, 1 insert #tt select 3, 2 declare @var varchar(10) set @var='1,2' select id from #tt where ','+@var+',' like '%,'+ltrim(类型)+',%' group by id having count(distinct 类型)=len(@var)-len(replace(@var,',',''))+1 id ----------- 1 3(2 行受影响)
DECLARE @a table(a int,b INT) insert @a select 1, 1 union all select 1, 2 union all select 1, 4union all select 2, 2 union all select 2, 4union all select 3, 1 union all select 3, 2 select a from @a where b in(1,2) group by a having COUNT(distinct b)>1 /* a ----------- 1 3(2 行受影响) */
insert @a select 1, 1
union all select 1, 2
union all select 1, 4union all select 2, 2
union all select 2, 4union all select 3, 1
union all select 3, 2
SELECT distinct a FROM @a a WHERE b in(1,2) AND exists(SELECT 1 FROM @a WHERE a=a.a AND b<>a.b AND b in(1,2) )
--result
/*
a
-----------
1
3(所影响的行数为 2 行)*/
(
ID int,
类型 int
)
insert into TT select 1,1
insert into TT select 1,2
insert into TT select 1,4
insert into TT select 2,2
insert into TT select 2,4
insert into TT select 3,1
insert into TT select 3,2declare @sql varchar(50)
select ID,@sql=@sql+','+ltrim(类型) from #TTcreate function dbo.Getinfo(@ID int)
returns nvarchar(1000)
as
begin
declare @sql nvarchar(1000)
set @sql=''
select @sql=@sql+','+ltrim(类型) from TT where ID=@ID
return stuff(@sql,1,1,'')
enddeclare @str nvarchar(20)
set @str='1,2'
select distinct ID from
(
select distinct ID,dbo.Getinfo(ID) 类型 from TT
) tt
where charindex(@str,类型)>0ID
-----------
1
3(2 行受影响)
create table #tt(ID int,类型 int)
insert #tt select 1, 1
insert #tt select 1, 2
insert #tt select 1, 4insert #tt select 2, 2
insert #tt select 2, 4insert #tt select 3, 1
insert #tt select 3, 2
declare @var varchar(10)
set @var='1,2'
select id from #tt where ','+@var+',' like '%,'+ltrim(类型)+',%'
group by id
having count(distinct 类型)=len(@var)-len(replace(@var,',',''))+1 id
-----------
1
3(2 行受影响)
insert @a select 1, 1
union all select 1, 2
union all select 1, 4union all select 2, 2
union all select 2, 4union all select 3, 1
union all select 3, 2
select a
from @a
where b in(1,2)
group by a
having COUNT(distinct b)>1
/*
a
-----------
1
3(2 行受影响)
*/