代码之家 › 专栏 › 技术社区 › Roger26

Oracle中的复杂层次查询

oracle sql

Roger26 · 技术社区 · 4 年前

我的数据看起来像这样:

art_nr werbeart  leit  basis    
222    1           
333    2         222    
444    3               222
555    4         444   222

所以我有4个级别(werbeart列)。

级别2始终具有级别1的leit=art_nr

级别3始终具有级别1的base=art_nr

级别4总是具有级别3的leit=art_nr和级别1的basis=art_nr。

由于不同级别(leit/base)的列名不同,如何进行分层查询以显示从级别1到级别4的数据?

在Oracle中可以做到这一点吗?

谢谢!

0 回复 | 直到 4 年前

MT0 4 年前

您可以使用:

SELECT *
FROM   table_name
START WITH werbeart = 1
CONNECT BY werbeart = PRIOR werbeart + 1
AND (  ( werbeart = 2 AND PRIOR art_nr = leit )
    OR ( werbeart = 3 AND PRIOR leit   = basis )
    OR ( werbeart = 4 AND PRIOR art_nr = leit AND PRIOR basis = basis )
    )
ORDER SIBLINGS BY art_nr;

对于样本数据:

CREATE TABLE table_name ( art_nr, werbeart, leit, basis ) AS
SELECT 222, 1, NULL, NULL FROM DUAL UNION ALL           
SELECT 333, 2, 222,  NULL FROM DUAL UNION ALL    
SELECT 444, 3, NULL, 222  FROM DUAL UNION ALL
SELECT 555, 4, 444,  222  FROM DUAL UNION ALL
SELECT 123, 1, NULL, NULL FROM DUAL UNION ALL           
SELECT 234, 2, 123,  NULL FROM DUAL UNION ALL    
SELECT 345, 3, NULL, 123  FROM DUAL UNION ALL    
SELECT 567, 3, NULL, 123  FROM DUAL UNION ALL
SELECT 456, 4, 345,  123  FROM DUAL UNION ALL
SELECT 678, 4, 567,  123  FROM DUAL;

输出:

ART_NR | WERBEART | LEIT | BASIS
-----: | -------: | ---: | ----:
   123 |        1 | null |  null
   234 |        2 |  123 |  null
   345 |        3 | null |   123
   456 |        4 |  345 |   123
   567 |        3 | null |   123
   678 |        4 |  567 |   123
   222 |        1 | null |  null
   333 |        2 |  222 |  null
   444 |        3 | null |   222
   555 |        4 |  444 |   222

db<>小提琴 here

Gordon Linoff 4 年前

我不明白为什么需要分层查询。如果你只需要指定级别,你可以这样做:

select t.*, 1 as level, art_nr as highest
from t
where leit is null and basis is null
union all
select t.*, 2 as level, leit
from t
where leit is not null and basis is null
union all
select t.*, 3 as level, basis
from t
where leit is null and basis is not null
union all
select t.*, 4 as level, basis
from t
where leit is not null and basis is not null;

或者更简单地说:

select t.*,
       (case when leit is null and basis is null then 1
             when leit is not null and basis is null then 2
             when leit is null and basis is not null then 3
             else 4
        end) as level,
       (case when leit is null and basis is null then art_nr
             when leit is not null and basis is null then level
             when leit is null and basis is not null then basis
             else basis
        end) as highest
from t
order by highest, level;