自参照表的SQL查询来行
我有这样的Oracle 11g查询:自参照表的SQL查询来行
SELECT RPAD(' ', 2 * (T.ID_LEVEL - 1)) || T.IDE IDE, T.ID_LEVEL, T.CODE, T.FK_IDE
FROM TEST_DYNAMIC T
START WITH T.FK_IDE = 0
CONNECT BY NOCYCLE PRIOR T.IDE = T.FK_IDE
ORDER BY T.IDE,T.FK_IDE;
返回此数据:
IDE |IDE_LEVEL |CODE |FK_IDE |
-----|-----------|------|--------|
1 | 1|A01 | 0|
2 | 2|A01 | 1|
3 | 3|A01 | 2|
4| 4|A01 | 3|
5 | 2|A02 | 1|
6 | 2|A03 | 1|
7 | 3|A01 | 6|
8 | 1|A02 | 0|
正如你所看到的,是从自我refrencing表得到的数据,其中IDE_LEVEL列是包含此值(1,2,3,4)作为PK的主表的外键,而IDE列是来自TEST_DYNAMIC表的自动增量PK。
有一种方法以该结果转换为这一个?:
IDE |CODE_LEVEL1 |CODE_LEVEL2 |CODE_LEVEL3 |CODE_LEVEL4 |
-----|-------------|-------------|-------------|-------------|
1 |A01 |A01 |A01 |A01 |
1 |A01 |A02 |NULL |NULL |
1 |A01 |A03 |A01 |NULL |
8 |A02 |NULL |NULL |NULL |
在预期的结果如上所述,IDE列被示出对应于在FK_COLUMN三个ocurrences关键1三次,和第一个结果集中键8的一次(这一个没有子元素,因此必须显示在结果集中)。
任何帮助将不胜感激。
起初我没有得到这个问题。你不需要PIVOT,你需要在不同的列中显示每个分支的路径。无论如何,根据级别的数量,你可能需要一些动态的sql
with test_dynamic as (
select 1 ide, 1 id_level, 'A01' code, 0 fk_ide from dual
union all select 2, 2, 'A01', 1 from dual
union all select 3, 3, 'A01', 2 from dual
union all select 4, 4, 'A01', 3 from dual
union all select 5, 2, 'A02', 1 from dual
union all select 6, 2, 'A03', 1 from dual
union all select 7, 3, 'A01', 6 from dual
union all select 8, 1, 'A02', 0 from dual
),
tree as (
select code, level lvl, sys_connect_by_path(code, '/') path, connect_by_isleaf lf, connect_by_root(ide) root_ide
from test_dynamic t
start with t.fk_ide = 0
connect by nocycle prior t.ide = t.fk_ide
)
select root_ide, path,
regexp_substr(path, '[^/]+', 1, 1),
regexp_substr(path, '[^/]+', 1, 2),
regexp_substr(path, '[^/]+', 1, 3),
regexp_substr(path, '[^/]+', 1, 4)
from tree where lf = 1;
令人惊叹! 这就是解决方案! 我不知道你在查询中写的connect_by函数,但现在我知道了。呵呵呵。动态sql也是需要的,但这对我来说是一项工作。 我欠你一个人。 非常感谢你!!问候。 – iperezmel78 2014-10-17 17:50:41
如果级别的数量是动态的,那么无论如何您都需要使用动态SQL来创建具有动态列的查询。
至少有2种方法如何创建这样一个查询:
1)甲骨文与PIVOT(因为11克我想)
with test_dynamic as (
select 1 ide, 1 id_level, 'A01' code, 0 fk_ide from dual
union all select 2, 2, 'A01', 1 from dual
union all select 3, 3, 'A01', 2 from dual
union all select 4, 4, 'A01', 3 from dual
union all select 5, 2, 'A02', 1 from dual
union all select 6, 2, 'A03', 1 from dual
union all select 7, 3, 'A01', 6 from dual
union all select 8, 1, 'A02', 0 from dual
),
tree as (
select level lvl, t.ide, t.code
from test_dynamic t
start with t.fk_ide = 0
connect by nocycle prior t.ide = t.fk_ide
),
pivot_data as (
select * from tree pivot xml (max(code) lvl_code for lvl in (select distinct lvl from tree))
)
select ide,
extractvalue(lvl_xml,'/PivotSet/item[1]/column[2]') code_level1,
extractvalue(lvl_xml,'/PivotSet/item[2]/column[2]') code_level2,
extractvalue(lvl_xml,'/PivotSet/item[3]/column[2]') code_level3,
extractvalue(lvl_xml,'/PivotSet/item[4]/column[2]') code_level4
from pivot_data t order by 1;
(人们说,extractValue一起有可能成为过时的和推荐使用XMLTABLE,但我从来没有与这个工作)
2)的Oracle DECODE
with test_dynamic as (
select 1 ide, 1 id_level, 'A01' code, 0 fk_ide from dual
union all select 2, 2, 'A01', 1 from dual
union all select 3, 3, 'A01', 2 from dual
union all select 4, 4, 'A01', 3 from dual
union all select 5, 2, 'A02', 1 from dual
union all select 6, 2, 'A03', 1 from dual
union all select 7, 3, 'A01', 6 from dual
union all select 8, 1, 'A02', 0 from dual
),
tree as (
select level lvl, t.ide, t.code
from test_dynamic t
start with t.fk_ide = 0
connect by nocycle prior t.ide = t.fk_ide
)
select ide,
max(decode(lvl, 1, code, null)) code_level1,
max(decode(lvl, 2, code, null)) code_level2,
max(decode(lvl, 3, code, null)) code_level3,
max(decode(lvl, 4, code, null)) code_level4
from tree group by ide order by 1;
嗨Multisync。 完全同意您推荐的动态sql部分。 我测试了发送的查询,但都返回8行,只需要三个,它代表可以在值为1的FK_IDE列中看到的根父级的每个孩子。无论如何,感谢您的答复。问候。 – iperezmel78 2014-10-17 15:30:08
@ iperezmel78我不明白 - 为什么ide = 5,6,7的行必须忽略? – Multisync 2014-10-17 16:30:02
@ iperezmel78,好吧,我想我知道了 - 在结果数据透视表中,每行代表根的分支。目前尚不清楚,说实话。 – Multisync 2014-10-17 16:45:44
查询和数据结果是否正确? – itsols 2014-10-17 00:19:50
是的,他们是。 FK_IDE列用于父验证。当为0时,则其他root是来自另一个不是root的父节点的子节点。 – iperezmel78 2014-10-17 00:37:44
所以你想旋转你当前的结果集?你正在使用哪个版本?这些是使用'case'的版本高达10g的例子,使用'pivot'向上的版本高达11g。 – 2014-10-17 07:19:31