--源表(300万 记录,一个分区段)
create table T_a
(
C1 number(16),
C2 number(10),
property_01 varchar2(16), --属性代码
property_02 varchar2(16),
property_03 varchar2(16),
property_04 varchar2(16),
...
property_40 varchar2(16)
)--字典(4万 记录 ,每类记录数不定,多则上万,少则不超过10条)
create table T_b
(col_name varchar2(30), --T_a表的属性字段名,如'property_01'(大类)
property_value varchar2(16), --属性代码
property_desc varchar2(200) --属性描述
)
--结果表
create table T_a_desc
(
C1 number(16),
C2 number(10),
property_01_desc varchar2(200),
property_02_desc varchar2(200),
property_03_desc varchar2(200),
property_04_desc varchar2(200),
...
property_40_desc varchar2(200)
)现需将 T_a 表的 property_01-40 40个属性代码转译为T_b表中的property_desc,求个转译好方法
create table T_a
(
C1 number(16),
C2 number(10),
property_01 varchar2(16), --属性代码
property_02 varchar2(16),
property_03 varchar2(16),
property_04 varchar2(16),
...
property_40 varchar2(16)
)--字典(4万 记录 ,每类记录数不定,多则上万,少则不超过10条)
create table T_b
(col_name varchar2(30), --T_a表的属性字段名,如'property_01'(大类)
property_value varchar2(16), --属性代码
property_desc varchar2(200) --属性描述
)
--结果表
create table T_a_desc
(
C1 number(16),
C2 number(10),
property_01_desc varchar2(200),
property_02_desc varchar2(200),
property_03_desc varchar2(200),
property_04_desc varchar2(200),
...
property_40_desc varchar2(200)
)现需将 T_a 表的 property_01-40 40个属性代码转译为T_b表中的property_desc,求个转译好方法
SELECT nvl(tab_a.no_name,tab_a_desc.no_name) col_name
,tab_a.no_value PROPERTY_value
,tab_a_desc.no_desc PROPERTY_desc
FROM
-- 转换 tab_a
(SELECT
CASE WHEN b.rn = 1 THEN 'PROPERTY_01'
WHEN b.rn = 2 THEN 'PROPERTY_02'
WHEN b.rn = 3 THEN 'PROPERTY_03'
WHEN b.rn = 4 THEN 'PROPERTY_04' END no_name,
CASE WHEN b.rn = 1 THEN a.PROPERTY_01
WHEN b.rn = 2 THEN a.PROPERTY_02
WHEN b.rn = 3 THEN a.PROPERTY_03
WHEN b.rn = 4 THEN a.PROPERTY_04 END no_value
FROM t_a a,
(SELECT LEVEL rn FROM dual CONNECT BY LEVEL <= 4) b
) tab_a FULL OUTER JOIN
-- 转换 tab_a_desc
(SELECT
CASE WHEN b.rn = 1 THEN 'PROPERTY_01'
WHEN b.rn = 2 THEN 'PROPERTY_02'
WHEN b.rn = 3 THEN 'PROPERTY_03'
WHEN b.rn = 4 THEN 'PROPERTY_04' END no_name,
CASE WHEN b.rn = 1 THEN a.PROPERTY_01_DESC
WHEN b.rn = 2 THEN a.PROPERTY_02_DESC
WHEN b.rn = 3 THEN a.PROPERTY_03_DESC
WHEN b.rn = 4 THEN a.PROPERTY_04_DESC END no_desc
FROM T_a_desc a,
(SELECT LEVEL rn FROM dual CONNECT BY LEVEL <= 4) b
) tab_a_desc
ON tab_a.no_name = tab_a_desc.no_name;
另外,修正一下SQL
SELECT DISTINCT nvl(tab_a.no_name,tab_a_desc.no_name) col_name
,tab_a.no_value PROPERTY_value
,tab_a_desc.no_desc PROPERTY_desc
FROM
-- 转换 tab_a
(SELECT
CASE WHEN b.rn = 1 THEN 'PROPERTY_01'
WHEN b.rn = 2 THEN 'PROPERTY_02'
WHEN b.rn = 3 THEN 'PROPERTY_03'
WHEN b.rn = 4 THEN 'PROPERTY_04' END no_name,
CASE WHEN b.rn = 1 THEN a.PROPERTY_01
WHEN b.rn = 2 THEN a.PROPERTY_02
WHEN b.rn = 3 THEN a.PROPERTY_03
WHEN b.rn = 4 THEN a.PROPERTY_04 END no_value
FROM t_a a,
(SELECT LEVEL rn FROM dual CONNECT BY LEVEL <= 4) b
) tab_a FULL OUTER JOIN
-- 转换 tab_a_desc
(SELECT
CASE WHEN b.rn = 1 THEN 'PROPERTY_01'
WHEN b.rn = 2 THEN 'PROPERTY_02'
WHEN b.rn = 3 THEN 'PROPERTY_03'
WHEN b.rn = 4 THEN 'PROPERTY_04' END no_name,
CASE WHEN b.rn = 1 THEN a.PROPERTY_01_DESC
WHEN b.rn = 2 THEN a.PROPERTY_02_DESC
WHEN b.rn = 3 THEN a.PROPERTY_03_DESC
WHEN b.rn = 4 THEN a.PROPERTY_04_DESC END no_desc
FROM T_a_desc a,
(SELECT LEVEL rn FROM dual CONNECT BY LEVEL <= 4) b
) tab_a_desc
ON tab_a.no_name = tab_a_desc.no_name
-- 需要进一步的关联,否则会存在一对多的情况
WHERE nvl(tab_a.no_name,tab_a_desc.no_name) IS NOT NULL
ORDER BY col_name;
可以如下:select a.c1,a.c2,b1.property_desc ,b2.property_desc ... from T_a a
left join T_b b1 on a.property_01 = b1.property_value and b1.col_name ='property_01'
left join T_b b2 on a.property_01 = b2.property_value and b2.col_name ='property_02'
.....