sql - 在 Oracle 中的每个级别计算最小值的分层查询

Question

几天来我一直断断续续地研究这个问题，但一无所获。

我有一个类似这样的数据集:

NAME                EXPIRATION          PARENT_ID    CLASS_ID           
Master Class           365                              1               
Second Class           366                  1           2                 
Third Class            355                  2           3                 
Fourth Class           1001                 2           4                 
Fifth Class            1000                 4           5
Sixth Class            999                  4           6  

等等。等等。

我可以使用分层查询来查看某个类下需要哪些类。

我真正想知道的是层次结构的当前级别和子级别的最短到期日期是多少。过期时间是其下任何内容的最短过期时间。

如果我想以蛮力方式执行此操作，我可以取回分层查询的结果，然后对每一行运行一个如下所示的查询:

select min(expiration_date)
from ( start with class_id = $EACH_CLASS_ID_FROM_PREVIOUS_QUERY
connect by prior parent_id = class_id);

我正在想象这样的结果:

NAME             EXPIRATION                           CLASS_ID
Master Class      355 (Min of it or anything under it)     1
Second Class      355  ""                                  2
Third Class       355  ""                                  3
Fourth Class      999  ""                                  4
Fifth Class       1000 ""                                  5
Sixth Class       999  ""                                  6

不过我假设有更好的方法？也许吧？

感谢您的帮助，这几天我一直在困惑。

Answer 1

你的 table :

SQL> create table mytable (name,expiration,parent_id,class_id)
  2  as
  3  select 'Master Class', 365, null, 1 from dual union all
  4  select 'Second Class', 366, 1, 2 from dual union all
  5  select 'Third Class', 355, 2, 3 from dual union all
  6  select 'Fourth Class', 1001, 2, 4 from dual union all
  7  select 'Fifth Class', 1000, 4, 5 from dual union all
  8  select 'Sixth Class', 999, 4, 6 from dual
  9  /

Table created.

使用旧的语法连接:

SQL> with t as
  2  ( select connect_by_root class_id as class_id
  3         , connect_by_root name as name
  4         , expiration
  5      from mytable
  6   connect by parent_id = prior class_id
  7  )
  8  select class_id
  9       , name
 10       , min(expiration)
 11    from t
 12   group by class_id
 13       , name
 14   order by class_id
 15  /

  CLASS_ID NAME         MIN(EXPIRATION)
---------- ------------ ---------------
         1 Master Class             355
         2 Second Class             355
         3 Third Class              355
         4 Fourth Class             999
         5 Fifth Class             1000
         6 Sixth Class              999

6 rows selected.

如果您使用的是 11g 第 2 版或更高版本，则可以使用递归子查询分解:

SQL> with all_paths (root_class_id,root_name,class_id,expiration) as
  2  ( select class_id
  3         , name
  4         , class_id
  5         , expiration
  6      from mytable
  7     union all
  8    select ap.root_class_id
  9         , ap.root_name
 10         , t.class_id
 11         , t.expiration
 12      from mytable t
 13           inner join all_paths ap on (t.parent_id = ap.class_id)
 14  )
 15  select root_class_id as class_id
 16       , root_name as name
 17       , min(expiration)
 18    from all_paths
 19   group by root_class_id
 20       , root_name
 21   order by class_id
 22  /

  CLASS_ID NAME         MIN(EXPIRATION)
---------- ------------ ---------------
         1 Master Class             355
         2 Second Class             355
         3 Third Class              355
         4 Fourth Class             999
         5 Fifth Class             1000
         6 Sixth Class              999

6 rows selected.