急，sql查询，在线等

[lxb_090]

急，Sql语句
OrdersID 　　cid 　　　pid

1 　　　　　C01 　　　P01

2 　　　　　C02 　　　P02
3 　　　　　C01 　　　P07


… … … … …
以上为一个订单表（表名：orders），其中OrdersID为 订单号，pid 为 所订购商品的id号，cid为 订购该商品的 顾客 的id号。
请写一段SQL语句，找出 既订购了产品p01又订购了产品p07的顾客的cid值。

[lxb_090]

大家帮帮我啊

[wangdehao]

select distinct cid from tb a
where pid = 'p01' and exists(select * from tb where cid = a.cid and pid = 'p07')

[paoluo]

Select Distinct
A.cid
From orders A
Inner Join orders B
On A.cid=B.cid
Where A.pid='p01' And B.pid='p07'

[wgsasd311]

select distinct cid from orders a
where a.pid='p01' and exists(select 1 from orders where cid=a.cid and pid='007' )

[netcup]

select cid from orders where pid in('p01'.'p07')

[ping3000]

declare @a table(OrdersID int ,cid nvarchar(10),pid varchar(10))

insert into @a(OrdersID,cid,pid)
select 1,'C01','P01' union all
select 1,'C02','P02' union all
select 1,'C01','P07'

select cid from @a where pid = 'P01' and cid in (select cid from @a where pid = 'P07')

[gohst001]

select cid from t where pid = 'p01' and cid in (select cid from t where pid= 'p07')

[paoluo]

用關聯的效率要更高些

[lxb_090]

谢谢大家，我都试了，结果正确
select distinct cid from orders a
where a.pid='p01' and exists(select 1 from orders where cid=a.cid and pid='007' )
这个好象不对