现有如下两张表
com_tree
ID | LEVEL1 | LEVEL2 | LEVEL3 | LEVEL4 | LEVEL5 |
1 | 001 | 001 | 001 | 001 | 001 |
2 | 001 | 002 | 002 | 002 | 002 |
3 | 001 | 003 | 003 | 003 | 003 |
4 | 001 | 004 | 004 | 004 | 004 |
5 | 001 | 390 | 007 | 007 | 007 |
6 | 001 | 101 | 101 | 101 | 101 |
7 | 001 | 201 | 101 | 101 | 101 |
8 | 001 | 101 | 102 | 102 | 102 |
com_desc
ID | CODE | DESCPTIONS |
1 | 001 | 股份公司 |
2 | 002 | 工程物流事业部 |
3 | 003 | 特种物流事业部 |
4 | 004 | 股份船代公司 |
5 | 005 | 中瑞检验 |
6 | 006 | 山东力神 |
7 | 007 | 重庆物流 |
8 | 008 | 新陆桥 |
需要实现如下图的需求:
ID | LEVEL1 | LEVEL1_DESC | LEVEL2 | LEVEL2_DESC | LEVEL3 | LEVEL3_DESC | LEVEL4 | LEVEL4_DESC | LEVEL5 | LEVEL5_DESC |
1 | 001 | 股份公司 | 001 | 股份公司 | 001 | 股份公司 | 001 | 股份公司 | 001 | 股份公司 |
6 | 001 | 股份公司 | ||||||||
2 | 001 | 股份公司 | 002 | 工程物流事业部 | 002 | 工程物流事业部 | 002 | 工程物流事业部 | 002 | 工程物流事业部 |
4 | 001 | 股份公司 | 004 | 股份船代公司 | 004 | 股份船代公司 | 004 | 股份船代公司 | 004 | 股份船代公司 |
5 | 001 | 股份公司 | 007 | 重庆物流 | 007 | 重庆物流 | 007 | 重庆物流 | ||
8 | 001 | 股份公司 | ||||||||
3 | 001 | 股份公司 | 003 | 特种物流事业部 | 003 | 特种物流事业部 | 003 | 特种物流事业部 | 003 | 特种物流事业部 |
怎么办呢?很多人拿到之后一想法就是写一get_desc的函数,然后调用多次就ok了。这种办法虽然简单易行,但是很明显效率不行(考虑recursive calls吧)
有没有其它办法呢?当然有了,我们把com_tree里的列变成行,然后和com_desc关联,最后在把结果变成列不就OK了么?
这里我们需要用到列转行以及行转列,不会的同学们请自行搜索吧!
本次我们不采用经典写法了,用用oracle 11g提供的pivot及unpivot函数吧。
第一步,对com_tree进行列转行并关联com_desc,使用unpivot函数
代码如下:
select a.id,a.lvl,a.code as code1,b.descptions from
(select * from com_tree
unpivot
(
code for lvl in (level1 as 1,level2 as 2,level3 as 3,level4 as 4,level5 as 5)
)) a,
com_desc b
where a.code=b.code
注:in中的level请自行根据自己的情况进行添加,为了便于拓展可以考虑预留几个level
第二步,对第一步的结果集进行行转列即可
select * from
(
select a.id,a.lvl,a.code as code1,b.descptions from
(select * from com_tree
unpivot
(
code for lvl in (level1 as 1,level2 as 2,level3 as 3,level4 as 4,level5 as 5)
)) a,
com_desc b
where a.code=b.code
)
pivot(
max(code1),max(descptions) as "DESC" for lvl in (1 as level1,2 as level2,3 as level3,4 as level4,5 as level5)
)
至此大功告成!
最后附测试表的脚本:
create table COM_DESC
(
id INTEGER,
code VARCHAR2(10),
descptions VARCHAR2(30)
);
create table COM_TREE
(
id INTEGER,
level1 VARCHAR2(10),
level2 VARCHAR2(10),
level3 VARCHAR2(10),
level4 VARCHAR2(10),
level5 VARCHAR2(10)
)
;
insert into COM_DESC (id, code, descptions)
values (1, '001', '股份公司');
insert into COM_DESC (id, code, descptions)
values (2, '002', '工程物流事业部');
insert into COM_DESC (id, code, descptions)
values (3, '003', '特种物流事业部');
insert into COM_DESC (id, code, descptions)
values (4, '004', '股份船代公司');
insert into COM_DESC (id, code, descptions)
values (5, '005', '中瑞检验');
insert into COM_DESC (id, code, descptions)
values (6, '006', '山东力神');
insert into COM_DESC (id, code, descptions)
values (7, '007', '重庆物流');
insert into COM_DESC (id, code, descptions)
values (8, '008', '新陆桥');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (1, '001', '001', '001', '001', '001');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (2, '001', '002', '002', '002', '002');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (3, '001', '003', '003', '003', '003');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (4, '001', '004', '004', '004', '004');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (5, '001', '390', '007', '007', '007');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (6, '001', '101', '101', '101', '101');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (7, '001', '201', '101', '101', '101');
insert into COM_TREE (id, level1, level2, level3, level4, level5)
values (8, '001', '101', '102', '102', '102');
commit;