表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语句呢
解决方案 »
- 用sql一行数据变成多行数据
- 安装oracle后有5个相关的服务?哪三个是必须启动的
- 字符型转换(99:99)
- 关于树状数据结构,向各位高手求救,在线等
- 组合问题
- 各位我想實現如下功能,即在一個表的TRIGGER中改動自己的記錄,SQL SERVER是可以的,可是ORACLE不行,報ORA-04091的錯,請問該如何解決???
- redhat9下安装oracle8,急救!!
- 请教:PLSQL如何加入临时表的创建脚本
- 从oracle8.1.6(for linux)用exp 导出来的.dmp文件在oracle9.0.1上怎么imp时出错?各位看看
- 如何控制SQL PLUS的查询输出?
- 一个删除语句的问题
- update语句问题
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