表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语句呢
------解决方案--------------------------------------------------------
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
------解决方案--------------------------------------------------------
你这样的写法，我还是第一次见到。我测试下了，结果正确。学习了