/*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
[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是用的什么方式表示，你可以试一下。
[code="sql"]
select c1.* from (SELECT * from T_A a inner join T_C c on a.aid=c.aid where a.name='b') a2
,
T_C c1
where a2.aid<>c1.aid and c1.bid=a2.bid
[/code]
你这个需求应该一条语句解决不了 该使用sql编程
select a2.name from a as a2, c as c2 where a2.aid = c2.aid and c.bid exist (
select c.bid from a,c where a.name='b' and c.aid = a.aid) and a2.name <> 'b'
没运行，只是用SQL写了个大概思路。先求出不买的水果。然后求买了这些水果的人，最后把b排除。
[code="sql"]select c1.* from (SELECT a.aid,c.cid,c.bid from a a inner join c c on a.aid=c.aid where a.name='b') a2
,
c c1
where a2.aid<>c1.aid and c1.bid=a2.bid[/code]
为什么会只有e了？b买的水果是3和4你看c表中和b买相同水果的应该有1,4,5
知道你描述的意思了。是要和Ｂ买得一摸一样。比如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的转换成一个字符串4,5。然后比对是否有相等的就成。不过不定行转列不同数据库实现不同。很多数据库需要其专有关键字或者自定义函数。