22,210
社区成员
发帖
与我相关
我的任务
分享
DECLARE @a table(a int,b INT)
insert @a select 1, 1
union all select 1, 2
union all select 1, 4
union all select 2, 2
union all select 2, 4
union 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 行受影响)
*/
create table #tt(ID int,类型 int)
insert #tt select 1, 1
insert #tt select 1, 2
insert #tt select 1, 4
insert #tt select 2, 2
insert #tt select 2, 4
insert #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 行受影响)
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,2
declare @sql varchar(50)
select ID,@sql=@sql+','+ltrim(类型) from #TT
create 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,'')
end
declare @str nvarchar(20)
set @str='1,2'
select distinct ID from
(
select distinct ID,dbo.Getinfo(ID) 类型 from TT
) tt
where charindex(@str,类型)>0
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, 4
union all select 2, 2
union all select 2, 4
union 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 行)
*/