oracle 多表拼接,多个表数据拼接问题

最初由 juan025 发布

[B]这个就要用到分析函数了

用row_number()来做关联,另外自然还要用到了外连接

-- 数据准备

[php]

create table t_1(id number(1),c1 char(1));

insert into t_1 values(1,'a');

insert into t_1 values(1,'b');

insert into t_1 values(2,'c');

create table t_2(id number(1),c2 char(1));

insert into t_2 values(1,'b');

insert into t_2 values(2,'c');

insert into t_2 values(2,'d');

create table t_3(id number(1),c3 char(1));

insert into t_3 values(1,'a');

insert into t_3 values(1,'b');

insert into t_3 values(3,'c');

[/php]

-- 代码执行

[php]

select nvl(t4.id,t3.id) id,c1,c2,c3 from(

select nvl(t1.rn,t2.rn) rn,nvl(t1.id,t2.id) id,c1,c2 from

(select row_number() over(partition by id order by id)||id rn,id,c1 from t_1) t1,

(select row_number() over(partition by id order by id)||id rn,id,c2 from t_2) t2

where t1.rn(+) = t2.rn

union

select nvl(t1.rn,t2.rn) rn,nvl(t2.id,t1.id) id,c1,c2 from

(select row_number() over(partition by id order by id)||id rn,id,c1 from t_1) t1,

(select row_number() over(partition by id order by id)||id rn,id,c2 from t_2) t2

where t1.rn = t2.rn(+)) t4,(select row_number() over(partition by id order by id)||id rn,id,c3 from t_3) t3

where t3.rn(+) = t4.rn

union

select nvl(t4.id,t3.id) id,c1,c2,c3 from(

select nvl(t1.rn,t2.rn) rn,nvl(t1.id,t2.id) id,c1,c2 from

(select row_number() over(partition by id order by id)||id rn,id,c1 from t_1) t1,

(select row_number() over(partition by id order by id)||id rn,id,c2 from t_2) t2

where t1.rn(+) = t2.rn

union

select nvl(t1.rn,t2.rn) rn,nvl(t2.id,t1.id) id,c1,c2 from

(select row_number() over(partition by id order by id)||id rn,id,c1 from t_1) t1,

(select row_number() over(partition by id order by id)||id rn,id,c2 from t_2) t2

where t1.rn = t2.rn(+)) t4,(select row_number() over(partition by id order by id)||id rn,id,c3 from t_3) t3

where t3.rn = t4.rn(+)

[/php]

-- 结果

[php]

ID C1 C2 C3

---------- -- -- --

1 a  b  a

1 b     b

2 c  c

2    d

3       c

[/php]

哈哈，超长的代码，用了两次全连接 [/B]