求助SQL：多表查询汇总展示问题

[staryuxinyuan]

统计机构下发出订单、接收订单、作废订单的单号，先查询发出订单、接收订单、作废订单列表，再汇总到机构上，展示如图：
以下模拟4张临时表，求助大神有没有好的查询解决方案，Oracle版本11g。
with t0 as (select '北京' as a from dual),
t1 as(select '北京' as a, 1 as b from dual union select '北京' as a, 2 as b from dual),
t2 as (select '北京' as a, 3 as c from dual union select '北京' as a, 4 as c from dual union select '北京' as a, 5 as d from dual),
t3 as (select '北京' as a, 6 as d from dual)

[yaiger]

完全没看懂，真的

[staryuxinyuan]

引用 1 楼 yaiger 的回复:

完全没看懂，真的

我想把t2、t2、t3表的数据都通过字段“a”左连到t0表上，查询出来结果同excel表的展示

[nayi_224]

这个应该是全连

with t0 as (select '北京' as a from dual),
t1 as(select '北京' as a, 1 as b from dual union select '北京' as a, 2 as b from dual),
t2 as (select '北京' as a, 3 as c from dual union select '北京' as a, 4 as c from dual union select '北京' as a, 5 as d from dual),
t3 as (select '北京' as a, 6 as d from dual)

select t0.a, b, c, d from
(
select nvl(t0.a, t2.a) a, nvl(t0.rn, t2.rn) rn, b, c from 
(
select nvl(t0.a, t1.a) a, nvl(t0.rn, t1.rn) rn, b from
(select a, rownum rn from t0) t0
full join (select a, b, rownum rn from t1) t1 on (t0.a = t1.a and t0.rn = t1.rn)
) t0
full join (select a, c, rownum rn from t2) t2 on (t0.a = t2.a and t0.rn = t2.rn)
) t0
full join (select a, d, rownum rn from t3) t3 on (t0.a = t3.a and t0.rn = t3.rn)
;