--楼主是这个意思?
--在查询的时候,返回子表 value 为aa,cc 的记录create table #A(id int,name varchar(100),code varchar(100))
create table #B(id int,pid int,value varchar(100))
insert into #A(id,name,code)
select 1,'a','001' union all
select 2,'b','002'insert into #B(id,pid,value)
select 1,1,'aa' union all
select 2,1,'bb' union all
select 3,1,'cc' union all
select 4,2,'aa ' union all
select 5,2,'dd 'select t1.*,t2.value
from #A t1 inner join #B t2 on t1.id=t2.pid
where t2.value in ('aa','cc')drop table #A,#B/*
id name code value
1 a 001 aa
1 a 001 cc
2 b 002 aa */
where B.value in ('aa','cc')
select a.* from 主表 a inner join 子表 b
on a.id=b.pid and b.value in (aa,cc)
id name code
1 a 001
create table #B(id int,pid int,value varchar(100))
insert into #A(id,name,code)
select 1,'a','001' union all
select 2,'b','002'insert into #B(id,pid,value)
select 1,1,'aa' union all
select 2,1,'bb' union all
select 3,1,'cc' union all
select 4,2,'aa ' union all
select 5,2,'dd 'select t1.*
from #A t1 inner join #B t2 on t1.id=t2.pid
where t2.value = 'aa' and exists (select * from #B where pid=t1.id and value='cc')drop table #A,#B/*
id name code
1 a 001
*/
where exists (
select 1 from 子表
where pid=a.id
and value='aa'
)
and exists (
select 1 from 子表
where pid=a.id
and value='cc'
)
a.*
from
主表 a
where
exists(select 1 from 子表 where pid=a.id and value='aa')
and
exists(select 1 from 子表 where pid=a.id and value='cc')
declare @B table(id int,pid int,value varchar(100))
insert into @A(id,name,code)
select 1,'a','001' union all
select 2,'b','002'insert into @B(id,pid,value)
select 1,1,'aa' union all
select 2,1,'bb' union all
select 3,1,'cc' union all
select 4,2,'aa ' union all
select 5,2,'dd '
declare @str varchar(100)
set @str='aa,cc'select
a.id,a.name,a.code
from
@A a,@B b
where
a.id=b.pid and charindex(','+b.value+',',','+@str+',')>0
group by
a.id,a.name,a.code
having
count(distinct b.value)=(len(@str)-len(replace(@str,',',''))+1)/*
id name code
------ ------ --------
1 a 001
*/
count-unique aggregate 运算不能以 uniqueidentifier 数据类型作为参数。
这怎么搞啊,NND
表一
id,name,code....
1 a 001
2 b 002表二
id,name
1 aa
2 bb
3 cc
4 dd表三,pid关联表一id,vid关联表二
id,pid,vid
1 1 1
2 1 2
3 1 3
4 2 1
5 2 4 我在输入vid的字符串 1,3,4 还输入一个参数2 我返回1,3,4中任意满足两个条件的行,也就是pid为1,2的两条数据都满足条件,输入参数有可能是1,也返回两条记录,为3,没有符合条件的记录返回,该怎么写