select stock.id,
(select name from code where code.id=stock.cpID and code.type=3),
(select name from code where code.id=stock.jtID and code.type=2),
(select name from code where code.id=stock.mdID and code.type=1),
fhsj
from stock;
(select name from code where code.id=stock.cpID and code.type=3),
(select name from code where code.id=stock.jtID and code.type=2),
(select name from code where code.id=stock.mdID and code.type=1),
fhsj
from stock;
from stock a,
(select * from code where type=1) b,
(select * from code where type=2) c,
(select * from code where type=3) d
where a.cpid=b.id
and a.jtid=c.id
and a.mdid=d.id;
(select a3 from a where a.a2=d.d2 and a1=3) cpID,
(select a3 from a where a.a2=d.d3 and a1=2) jtID,
(select a3 from a where a.a2=d.d4 and a1=1) mdID,
d5 fhsj
from d;ID CPID JTID MDID FHSJ
---------- ---------- ---------- ---------- ----------
000001 产品2 火车 北京 20050801
000002 产品2 飞机 上海 20050724
000003 产品4 飞机 广州 20050803
000004 产品1 火车 上海 20050801Executed in 0.016 secondsSQL>
max(case when b.id = a.cpid and b.type = 3 then b.name else NULL end) CPID,
max(case when b.id = a.jtid and b.type = 2 then b.name else NULL end) JTID,
max(case when b.id = a.mdid and b.type = 1 then b.name else NULL end) MDID,
a.fhsj
from stock a,code b
group by a.id,a.FHSJ;SQL> ID CPID JTID MDID FHSJ
------- ------- ------- ---------- -----------
000001 产品2 火车 北京 2005-8-1
000002 产品2 飞机 上海 2005-7-24
000003 产品4 飞机 广州 2005-8-1
000004 产品1 火车 上海 2005-8-1