表part数据如下:
PART SUPP1 SUPP2 SUPP3
-----------------------------------
P1 S1 S2 S3
P2 S2 S3
P3 S1 S3
P4 S1表supplier数据如下
SUPP SUPPLIER_NAME
---------------------------
S1 Supplier#1
S2 Supplier#2
S3 Supplier#3我想得到的数据集如下:
PART SUPP1 SUPP2 SUPP3
-----------------------------------------------------------
P1 Supplier#1 Supplier#2 Supplier#3
P2 Supplier#2 Supplier#3
P3 Supplier#1 Supplier#3
P4 Supplier#1应该如何写SQL语句呢
PART SUPP1 SUPP2 SUPP3
-----------------------------------
P1 S1 S2 S3
P2 S2 S3
P3 S1 S3
P4 S1表supplier数据如下
SUPP SUPPLIER_NAME
---------------------------
S1 Supplier#1
S2 Supplier#2
S3 Supplier#3我想得到的数据集如下:
PART SUPP1 SUPP2 SUPP3
-----------------------------------------------------------
P1 Supplier#1 Supplier#2 Supplier#3
P2 Supplier#2 Supplier#3
P3 Supplier#1 Supplier#3
P4 Supplier#1应该如何写SQL语句呢
select part,
(select SUPPLIER_NAME from supplier where supp = t.supp1) as supp1,
(select SUPPLIER_NAME from supplier where supp = t.supp2) as supp2,
(select SUPPLIER_NAME from supplier where supp = t.supp3) as supp3
from part t
with part as
(select 'P1' as PART,'S1' as SUPP1,'S2' as SUPP2,'S3' as SUPP3 from dual
union all
select 'P2' as PART,'S2' as SUPP1,'S3' as SUPP2, null as SUPP3 from dual
union all
select 'P3' as PART,'S1' as SUPP1,'S3' as SUPP2, null as SUPP3 from dual
union all
select 'P4' as PART,'S1' as SUPP1,null as SUPP2,null as SUPP3 from dual)
,
supplier as
(select 'S1' as SUPP, 'Supplier#1' AS SUPPLIER_NAME from dual
UNION ALL
select 'S2' as SUPP, 'Supplier#2' AS SUPPLIER_NAME from dual
union all
select 'S3' as SUPP, 'Supplier#3' AS SUPPLIER_NAME from dual
)
select part,
(select SUPPLIER_NAME from supplier where supp = t.supp1) as supp1,
(select SUPPLIER_NAME from supplier where supp = t.supp2) as supp2,
(select SUPPLIER_NAME from supplier where supp = t.supp3) as supp3
from part t
SELECT t.part,
CASE WHEN t.supp1='S1' THEN 'Supplier#1'
WHEN t.supp1='S2' THEN 'Supplier#2'
WHEN t.supp1='S3' THEN 'Supplier#3'
ELSE '' END supp1 ,
CASE WHEN t.supp2='S1' THEN 'Supplier#1'
WHEN t.supp2='S2' THEN 'Supplier#2'
WHEN t.supp2='S3' THEN 'Supplier#3'
ELSE '' END supp2 ,
CASE WHEN t.supp3='S1' THEN 'Supplier#1'
WHEN t.supp3='S2' THEN 'Supplier#2'
WHEN t.supp3='S3' THEN 'Supplier#3'
ELSE '' END supp3
FROM part T