求一条SQL语句，今天老壳有点发卡，忘了如何写

求一条SQL语句，今天老壳有点发卡，忘了怎么写
表1:

表2：

表1中Pid字段和表2中的Pid字段是关联的

现在想查询表2中Avid 等于11且等8的Pid，在表1中的数据。

------解决方案--------------------
select 表1.PID,表1.Name,表1.Model,表1.CID,表1.BID,表1.Mid from 表1 inner join 表2 on
表1.PID=表2.PID where 表2.AVID in(11,8)
------解决方案--------------------

SQL code


select  * from 表1 as a 
where exists(select 1 from 表2 where PID=a.PID and AVID=11)
and exists(select 1 from 表2 where PID=a.PID and AVID=8

------解决方案--------------------

SQL code

--> 测试数据：[ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([id] int,[name] varchar(1))
insert [ta]
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d'


--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] int)
insert [tb]
select 1,8 union all
select 1,9 union all
select 2,8 union all
select 2,11 union all
select 3,5 union all
select 3,8 union all
select 3,11

select * from [ta]
where id in(
select [id] from [tb] where [name] in(8,11)
group by [id] having count(distinct [name])=2
)

求一条SQL语句，今天老壳有点发卡，忘了如何写

相关推荐