--测试数据 CREATE TABLE [dbo].[TABLE1] ([f1] [int] NOT NULL ,[f2] [int] NOT NULL ) ALTER TABLE [dbo].[TABLE1] ADD CONSTRAINT [PK_TABLE1] PRIMARY KEY CLUSTERED([f1],[f2]) insert table1 select 30,1 union all select 30,2 union all select 30,3 union all select 31,1 union all select 31,3 union all select 32,2 union all select 32,3 go--定义查询参数 declare @值 varchar(10) set @值='2,3'select f1 from [TABLE1] where charindex(','+cast(f2 as varchar)+',',','+@值+',')>0 group by f1 having count(*)=len(@值)-len(replace(@值,',',''))+1 go--删除测试 drop table table1/*--测试结果f1 ----------- 30 32(所影响的行数为 2 行) --*/
create function getnametable(@s varchar(100)) returns @r table(f2 int) as begin declare @i int set @s=@s+',' set @i=charindex(',',@s) while @i>0 begin insert into @r select cast(substring(@s,1,@i-1) as int) set @s=stuff(@s,1,@i,'') set @i=charindex(',',@s) end return end goselect * from table1 a where not exists(select * from dbo.getnametable('2,3') b where b.f2 not in (select f2 from table1 where f1=a.f1))
declare @Para varchar(20) set @Para='2' declare @sql varchar(3000) declare @sql1 varchar(3000)if charindex(',',@para)>0 begin set @sql=' select f1 from table1 a where f2='+left(@Para,charindex(',',@para)-1) set @sql1=replace(right(@Para,len(@Para)-charindex(',',@para)+1),',',') and exists (select 1 from table1 where f1=a.f1 and f2=') set @sql1=right(@sql1,len(@sql1)-1)+')' set @sql=@sql+@sql1 end else set @sql=' select f1 from table1 a where f2='+@paraexec( @sql)
declare @in varchar(1000) set @in='2,3'select f1,count(distinct f2) from table1 where charindex(','+cast(f2 as varchar(10))+',',','+@i+',')>0 group by f1 having count(distinct f2)=len(@in)-replace(@in,',','')+1
CREATE TABLE [dbo].[TABLE1] ([f1] [int] NOT NULL ,[f2] [int] NOT NULL )
ALTER TABLE [dbo].[TABLE1] ADD CONSTRAINT [PK_TABLE1] PRIMARY KEY CLUSTERED([f1],[f2])
insert table1 select 30,1
union all select 30,2
union all select 30,3
union all select 31,1
union all select 31,3
union all select 32,2
union all select 32,3
go--定义查询参数
declare @值 varchar(10)
set @值='2,3'select f1
from [TABLE1]
where charindex(','+cast(f2 as varchar)+',',','+@值+',')>0
group by f1
having count(*)=len(@值)-len(replace(@值,',',''))+1
go--删除测试
drop table table1/*--测试结果f1
-----------
30
32(所影响的行数为 2 行)
--*/
returns @r table(f2 int)
as
begin
declare @i int
set @s=@s+','
set @i=charindex(',',@s)
while @i>0
begin
insert into @r select cast(substring(@s,1,@i-1) as int)
set @s=stuff(@s,1,@i,'')
set @i=charindex(',',@s)
end
return
end
goselect * from table1 a where
not exists(select * from dbo.getnametable('2,3') b where b.f2
not in (select f2 from table1 where f1=a.f1))
set @Para='2'
declare @sql varchar(3000)
declare @sql1 varchar(3000)if charindex(',',@para)>0
begin set @sql='
select f1
from table1 a
where f2='+left(@Para,charindex(',',@para)-1) set @sql1=replace(right(@Para,len(@Para)-charindex(',',@para)+1),',',') and exists (select 1 from table1 where f1=a.f1 and f2=')
set @sql1=right(@sql1,len(@sql1)-1)+')'
set @sql=@sql+@sql1
end
else
set @sql='
select f1
from table1 a
where f2='+@paraexec( @sql)
set @in='2,3'select f1,count(distinct f2) from table1
where charindex(','+cast(f2 as varchar(10))+',',','+@i+',')>0
group by f1
having count(distinct f2)=len(@in)-replace(@in,',','')+1