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

Question

有一张基本表。里边记录是；
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  ;   

Answer 1

--如果每个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
;

Answer 2

--另一种写法
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               雪梨

Answer 3

select a.personid personid,a.goodswant,b.personid personid2,b.goodswant goodswant2 from
(
select personid,max(decode(cate,'want',goods,null)) goodswant,max(decode(cate,'own',goods,null)) goodsown 
from turn group by personid
)a inner join 
(
select personid,max(decode(cate,'want',goods,null)) goodswant,max(decode(cate,'own',goods,null)) goodsown 
from turn group by personid
)b on a.goodswant = b.goodsown and a.goodsown = b.goodswant

Answer 4

每个personid有多个want和own的时候用以下这个 
select a.personid,a.goodswant,b.personid,a.goodsown from (
select distinct personid,goodswant,goodsown from (
  select personid,max(goodswant) goodswant,max(goodsown) goodsown from
  (
      select personid,goodswant,goodsown,row_number() over(partition by goodswant order by personid) sn from 
      (
      select personid,decode(cate,'want',goods,null) goodswant,decode(cate,'own',goods,null) goodsown  from turn
      )
      union 
        select personid,goodswant,goodsown,row_number() over(partition by goodsown order by personid) sn from 
      (
      select personid,decode(cate,'want',goods,null) goodswant,decode(cate,'own',goods,null) goodsown  from turn
      )
  ) group by personid,sn order by personid
)
) a,
(
select distinct personid,goodswant,goodsown from (
  select personid,max(goodswant) goodswant,max(goodsown) goodsown from
  (
      select personid,goodswant,goodsown,row_number() over(partition by goodswant order by personid) sn from 
      (
      select personid,decode(cate,'want',goods,null) goodswant,decode(cate,'own',goods,null) goodsown  from turn
      )
      union 
        select personid,goodswant,goodsown,row_number() over(partition by goodsown order by personid) sn from 
      (
      select personid,decode(cate,'want',goods,null) goodswant,decode(cate,'own',goods,null) goodsown  from turn
      )
  ) group by personid,sn order by personid
)
) b
where a.goodswant=b.goodsown and a.goodsown=b.goodswant

Answer 5

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' );
insert into turn values(5,'want','苹果','kelvin');
insert into turn values(6,'own','草莓','kelvin');
insert into turn values(7,'want','草莓','xiaoqiang');
insert into turn values(8,'own','苹果','xiaoqiang');