面试题——礼品交换，求sql

显示全部楼层 · 2011-2-18 11:43:32

有一张基本表。里边记录是；
id cate goods personid
1 want 雪梨 kelvin
2 own 香蕉 kelvin
3 want 香蕉 amy
4 own 雪梨 amy
两个人相互想交换东西，刚好互补了。结果就有了记录了
要的结果是：
交换者A 物品交换者B 物品
kelvin 香蕉 amy 雪梨

create table TURN
(
ID NUMBER,
CATE VARCHAR2(20),
GOODS VARCHAR2(20),
PERSONID VARCHAR2(20)
);
insert into turn values(1','want','雪梨','kelvin'));
insert into turn values(2','own','香蕉','kelvin' );
insert into turn values(3','want','香蕉','amy' );
insert into turn values(4','own','雪梨','amy' );
commit ;

千问 · 2011-2-18 11:43:32

--如果每个personid只有一个want和一个own就比较容易\，现在求更完整的写法
with t as
(select personid
,max(case when a.cate='want' then a.goods end) goods_want
,max(case when a.cate='own' then a.goods end) goods_own
from turn a
group by a.personid
)
select a.personid,a.goods_want,a.personid,b.goods_own
from t a,t b
where a.goods_want=b.goods_own
and a.goods_own=b.goods_want
;

千问 · 2011-2-18 11:43:32

--另一种写法
with t as
(select a.personid aid,b.personid bid,a.personid aid2,a.goods
from turn a,turn b
where a.cate='want'
and b.cate='own'
and a.goods=b.goods
)
select a.aid,a.goods goods_want,a.bid,b.goods goods_own
from t a,t b
where a.aid=b.bid
and a.bid=b.aid2
;

AID GOODS_WANT BID GOODS_OWN
-------------------- -------------------- -------------------- --------------------
kelvin 雪梨 amy 香蕉
amy 香蕉 kelvin 雪梨

千问 · 2011-2-18 11:43:32

首先明确下问题，
两个人相互想交换东西，刚好互补了。结果就有了记录了
要的结果是：
这里的两个人可以是自己和自己么？

千问 · 2011-2-18 11:43:32

如果只要求拥有物品子集互补，
with c AS (
SELECT a.catecate_a
,a.personidpersonid_a
,b.catecate_b
,b.personidpersonid_b
,a.goods goods
FROM TURN a
JOIN TURN b
ON a.goods = b.goods

AND a.personidb.personid

AND (a.cate = 'want' AND b.cate='own'

OR b.cate = 'want' AND a.cate='own'

)
)
SELECT c1.*
FROM c c1 JOIN c c2
ON c1.goods = c2.goods

AND c1.personid_a = c2.personid_b

AND c2.personid_a = c1.personid_b;

如果要求整个集合互补就麻烦点：
with c AS (
SELECT a.catecate_a
,a.goods goods_a
,a.personidpersonid_a
,b.catecate_b
,b.goods goods_b
,b.personidpersonid_b
FROM TURN a
JOIN TURN b
ON a.goods = b.goods

AND a.personid a2.personid
and a1.want = a2.own
and a1.own= a2.want
;

千问 · 2011-2-18 11:43:32

谢谢大家的回答
毕竟是写在纸上的面试题，我也不好意思问考官题目每一步具体意义
题目定义的不明确，也许就是想给我们更多的思考空间
不结合实例的话没有绝对标准的答案