匿名用户
1级
2016-08-25 回答
with t1 as
(
select 'ttt' optpmst_id, 'Task1' optpmst_sht from dual union
all
select 'ppp' optpmst_id, 'Task2' optpmst_sht from dual
),
t2
as
(
select 'ttt' optpmst_id, '1' optplin_seq, 'CC1' optptsk_sht from dual
union all
select 'ttt' optpmst_id, '2' optplin_seq, 'CC2' optptsk_sht from
dual union all
select 'ttt' optpmst_id, '3' optplin_seq, 'CC3' optptsk_sht
from dual union all
select 'ppp' optpmst_id, '1' optplin_seq, 'CC1'
optptsk_sht from dual union all
select 'ppp' optpmst_id, '2' optplin_seq,
'CC2' optptsk_sht from dual union all
select 'ppp' optpmst_id, '3'
optplin_seq, 'CC3' optptsk_sht from dual union all
select 'ppp' optpmst_id,
'4' optplin_seq, 'CC4' optptsk_sht from dual
),
t3 as
(
select
'ttt' optpmst_id, '1' dgr_seq, 'DG1' dgr_sht, 'RK1' dgr_dsc from dual union
all
select 'ttt' optpmst_id, '2' dgr_seq, 'DG2' dgr_sht, 'RK2' dgr_dsc from
dual union all
select 'ttt' optpmst_id, '3' dgr_seq, 'DG3' dgr_sht, 'RK3'
dgr_dsc from dual union all
select 'ttt' optpmst_id, '4' dgr_seq, 'DG4'
dgr_sht, '' dgr_dsc from dual union all
select 'ttt' optpmst_id, '5' dgr_seq,
'DG5' dgr_sht, '' dgr_dsc from dual union all
select 'ppp' optpmst_id, '1'
dgr_seq, 'DG1' dgr_sht, 'RK1' dgr_dsc from dual union all
select 'ppp'
optpmst_id, '2' dgr_seq, 'DG2' dgr_sht, 'RK2' dgr_dsc from dual
)
select t1.optpmst_sht,t2.optpmst_id,t2.optplin_seq, t2.optptsk_sht,
t3.dgr_seq, t3.dgr_sht, t3.dgr_dsc
from t1 inner join t2 on
t1.optpmst_id=t2.optpmst_id
full join t3 on t2.optpmst_id=t3.optpmst_id and
t2.optplin_seq=t3.dgr_seq
;
追问:
亲 这个效果挺好的!但是需要以orcl数据库 sql的形式,去查询对应的三张表内的数据。
追答:
上面三个临时表是测试用的。
你把
select t1.optpmst_sht,t2.optpmst_id,t2.optplin_seq, t2.optptsk_sht,
t3.dgr_seq, t3.dgr_sht, t3.dgr_dsc
from t1 inner join t2 on
t1.optpmst_id=t2.optpmst_id
full join t3 on t2.optpmst_id=t3.optpmst_id and
t2.optplin_seq=t3.dgr_seq
这个改成你自己的表和字段就可以了
追问:
我调整的时候,必须是表2记录比表3记录条目多,才能保证表3会完整导出,否则是已表2最大化为准导出的!
追答:
那这样的话,你的图片的结果是不对的。ttt的危险点4和5不应该显示出来。
select t1.optpmst_sht,t2.optpmst_id,t2.optplin_seq, t2.optptsk_sht,
t3.dgr_seq, t3.dgr_sht, t3.dgr_dsc
from t1 inner join t2 on
t1.optpmst_id=t2.optpmst_id
left join t3 on t2.optpmst_id=t3.optpmst_id and
t2.optplin_seq=t3.dgr_seq
追问:
图片想要的效果是对的!是我再你给的语句加了二个过滤条件,导致表3行不能全部显示了。能看下问题?
追答:
首先,之前我测试用的三个表是按照你的图片中创建的。结果没问题。
然后,你增加了过滤条件造成不能全部显示,这就需要看你这个条件跟你想要的数据的关系,从目前你提供的Sql看,你想要的三行的rmoptpmst.optyp_no不是46,所以出不来。因为 rmoptpmst 是查询的主表。即
select * from rmoptpmst where rmoptpmst.optyp_no='46';
结果不会出现你想要的哪行的optpmst_no.
最后,如果想调整你最后提供的SQL,需要提供这个SQL里用到的三个表格结构和数据以及三个表的管理关系。