一个SQL语句，求解

Question

/*a表(人) b表(水果)
aid name bid name
1 a 1 苹果
2 b 2 梨
3 c 3 橙
4 d 4 香蕉

c表
cid aid bid
1 1 1
2 1 3
3 1 4
4 2 3

5 2 4 问题:找出跟B 用户 买相同水果的人
6 3 2 SELECT c.BID FROM T_C C WHERE C.AID = (SELECT AID FROM T_A WHERE NAME = 'b')
7 4 1 我先找出了 b 买的水果是什么？然后应该这么求
8 4 2 急~~~~~~~~~~~~~~~~~~~~~急~~~~~~~~~~~~~~~~~~~~~急~~~~~~~~~~~~~~~~~~~~~
9 4 3
10 4 4
*/
[b]问题补充：[/b]
忘了补充2条数据

/*a表(人)

aid name

5 e

c表
cid aid bid

11 5 3
12 5 4

与 b 购买水果完全相同的人，答案应该是 E

[b]问题补充：[/b]
WITH condition AS (
SELECT C.BID,c.aid
FROM T_C C
WHERE C.AID = (SELECT AID FROM T_A WHERE NAME = 'b')
)

SELECT DISTINCT cc.aid,aa.name
FROM condition TT,
T_C CC,
t_a aa
WHERE CC.BID IN (TT.BID)

AND cc.aid NOT IN (tt.aid)
AND cc.aid NOT IN (SELECT cc.aid FROM t_c cc WHERE cc.bid <(SELECT MIN(bid) FROM condition))
AND cc.aid NOT IN (SELECT cc.aid FROM t_c cc WHERE cc.bid >(SELECT MAX(bid) FROM condition))
AND cc.aid = aa.aid

做出来了，不知道有没有更好的方法
[b]问题补充：[/b]
与b完全相同，我的意思是包括数量和种类都完全一样，所以只有e
[b]问题补充：[/b]
[quote]知道你描述的意思了。是要和Ｂ买得一摸一样。比如B只买了橙子和香蕉，找同样只买了橙子和香蕉的人，不知道我现在理解得对不对。刚才理解成只要买了B买过的就可以了。[/quote]

对，答案现在倒是做出来了，考虑到性能问题是否还有别的思路，要用EXISTS的话怎么能实现？
[b]问题补充：[/b]
[code="java"]SELECT DISTINCT cc.aid,aa.name
FROM (SELECT C.BID,c.aid
FROM T_C C
WHERE C.AID = (SELECT AID FROM T_A WHERE NAME = 'b')) TT,
T_C CC,
t_a aa
WHERE CC.BID IN (TT.BID)

AND cc.aid NOT IN (tt.aid)
AND cc.aid NOT IN (SELECT cc.aid FROM t_c cc WHERE cc.bid <(SELECT MIN(bid) FROM (SELECT C.BID,c.aid
FROM T_C C
WHERE C.AID = (SELECT AID FROM T_A WHERE NAME = 'b'))))
AND cc.aid NOT IN (SELECT cc.aid FROM t_c cc WHERE cc.bid >(SELECT MAX(bid) FROM (SELECT C.BID,c.aid
FROM T_C C
WHERE C.AID = (SELECT AID FROM T_A WHERE NAME = 'b'))))
AND cc.aid = aa.aid
[/code]

WITH是ORACLE的关键字，那样写只是看着清楚些，而且不需要重复查询了。
不太想使用行列转换，那样就写死了，如果B表有改动的话还要改SQL

Answer 1

[code="sql"]

select temp2.aid,temp2.name from
(SELECT a.aid,c.cid,c.bid,a.name from a a inner join c c on a.aid=c.aid where a.name!='b' group by a.aId) temp2
where temp2.aid not in
(
select a2.aid from
(SELECT a.aid,c.cid,c.bid,a.name from a a inner join c c on a.aid=c.aid ) a2
left join
(SELECT c.bid as 't' from a a inner join c c on a.aid=c.aid where a.name='b' ) c1
on a2.bid=c1.t

where c1.t is null group by a2.aid
)

[/code]
在mysql 下面可以通过，在ORACLE 下面我不知道is null是用的什么方式表示，你可以试一下。