oracle表连接问题

[Moriarty_徐]

表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语句呢

[dut703]

作业题？？？

[Moriarty_徐]

我的想法是写一个函数，传入SUPP得到SUPPLIER_NAME，这样做和3L的方法似乎差不多啊

[Moriarty_徐]

不是，项目中的问题，我把它简化了一下

[foolish_cc1986]

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

[foolish_cc1986]

补充个测试数据

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

[善若止水]

用case when 函数，我已经测试成功了 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

[善若止水]

引用 3 楼 foolish_cc1986 的回复:

SQL code?12345select part, (select SUPPLIER_NAME from supplier where supp = t.supp1) as supp1, (select SUPPLIER_NAME from supplier where supp = t.supp2) as supp2, (selec……

你这样的写法，我还是第一次见到。我测试下了，结果正确。学习了

[Cryking]

3L的是正解,5L的不通用,且效率不高,还不如写DECODE