Oracle中的递归查询以查找子级和同级 [英] Recursive query in Oracle to find children and sibling

问题描述

我正在努力进行分层SQL查询.我想在其子级和同级的disp_order中再添加2列.

I'm struggling a hierarchical SQL query. I want to have another 2 columns of the disp_order of its children and sibling.

孩子-应当保留孩子及其孙子以及所有孩子到目前为止的所有疾病.

Children - Should hold all disp_order of their child and their grand children and so far.

同级-应该保留具有相同父级的行的disp_order.

Sibling - Should hold the disp_order of the row having the same parent.

+------------+-----+-------------+--------+
| disp_order | lvl | description | parent |
+------------+-----+-------------+--------+
|     0      |  1  |      A      |        |
|     1      |  2  |      B      |    0   |
|     2      |  3  |      C      |    1   |
|     3      |  4  |      D      |    2   |
|     4      |  5  |      E      |    3   |
|     5      |  2  |      F      |    0   |
|     6      |  3  |      G      |    5   |
|     7      |  3  |      H      |    5   |
|     8      |  3  |      I      |    5   |
|     9      |  4  |      J      |    8   |
|     10     |  5  |      K      |    9   |
+------------+-----+-------------+--------+

结果应该是:

+------------+-----+-------------+--------+------------------------+---------+
| disp_order | lvl | description | parent |        children        | sibling |
+------------+-----+-------------+--------+------------------------+---------+
|     0      |  1  |      A      |        |   1,2,3,4,5,6,7,8,9,10 |         |
|     1      |  2  |      B      |    0   |          2,3,4         |    5    |
|     2      |  3  |      C      |    1   |           3,4          |         |
|     3      |  4  |      D      |    2   |            4           |         |
|     4      |  5  |      E      |    3   |                        |         |
|     5      |  2  |      F      |    0   |        6,7,8,9,10      |    1    |
|     6      |  3  |      G      |    5   |                        |   7,8   |
|     7      |  3  |      H      |    5   |                        |   6,8   |
|     8      |  3  |      I      |    5   |           9,10         |   6,7   |
|     9      |  4  |      J      |    8   |            10          |         |
|     10     |  5  |      K      |    9   |                        |         |
+------------+-----+-------------+--------+------------------------+---------+

这是我当前的查询:

SELECT t.*,
       ( SELECT MAX( disp_order )
         FROM   tbl_pattern p
         WHERE  p.lvl        = t.lvl - 1
         AND    p.disp_order < t.disp_order ) AS parent
FROM   tbl_pattern t

推荐答案

从您先前的问题继续:

SQL小提琴

Oracle 11g R2架构设置:

CREATE TABLE tbl_pattern ( order_no, code, disp_order, lvl, description ) AS
SELECT 'RM001-01', 1, 0, 1, 'HK140904-1A' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 1, 2, 'HK140904-1B' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 2, 3, 'HK140904-1B' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 3, 4, 'HK140904-1C' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 4, 5, 'HK140904-1D' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 5, 2, 'HK140904-1E' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 6, 3, 'HK140904-1E' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 7, 3, 'HK140904-1X' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 8, 4, 'HK140904-1E' FROM DUAL UNION ALL
SELECT 'RM001-01', 1, 9, 5, 'HK140904-1E' FROM DUAL;

查询1 :

WITH data ( order_no, code, disp_order, lvl, description, parent ) AS (
  SELECT t.*,
         ( SELECT MAX( disp_order )
           FROM   tbl_pattern p
           WHERE  p.order_no   = t.order_no
           AND    p.code       = t.code
           AND    p.lvl        = t.lvl - 1
           AND    p.disp_order < t.disp_order ) AS parent
  FROM   tbl_pattern t
)
SELECT d.*,
       ( SELECT LISTAGG( c.disp_order, ',' ) WITHIN GROUP ( ORDER BY c.disp_order )
         FROM   data c
         START WITH c.parent = d.disp_order
         AND        c.order_no = d.order_no
         AND        c.code     = d.code
         CONNECT BY PRIOR c.disp_order = c.parent
         AND        PRIOR c.order_no   = c.order_no
         AND        PRIOR c.code       = c.code 
       ) AS children,
       ( SELECT LISTAGG( c.disp_order, ',' ) WITHIN GROUP ( ORDER BY c.disp_order )
         FROM   data c
         WHERE  c.parent     = d.parent
         AND    c.disp_order <> d.disp_order
         AND    c.order_no   = d.order_no
         AND    c.code       = d.code
       ) AS siblings
FROM   data d

结果 :

Results:

| ORDER_NO | CODE | DISP_ORDER | LVL | DESCRIPTION | PARENT |          CHILDREN | SIBLINGS |
|----------|------|------------|-----|-------------|--------|-------------------|----------|
| RM001-01 |    1 |          0 |   1 | HK140904-1A | (null) | 1,2,3,4,5,6,7,8,9 |   (null) |
| RM001-01 |    1 |          1 |   2 | HK140904-1B |      0 |             2,3,4 |        5 |
| RM001-01 |    1 |          2 |   3 | HK140904-1B |      1 |               3,4 |   (null) |
| RM001-01 |    1 |          3 |   4 | HK140904-1C |      2 |                 4 |   (null) |
| RM001-01 |    1 |          4 |   5 | HK140904-1D |      3 |            (null) |   (null) |
| RM001-01 |    1 |          5 |   2 | HK140904-1E |      0 |           6,7,8,9 |        1 |
| RM001-01 |    1 |          6 |   3 | HK140904-1E |      5 |            (null) |        7 |
| RM001-01 |    1 |          7 |   3 | HK140904-1X |      5 |               8,9 |        6 |
| RM001-01 |    1 |          8 |   4 | HK140904-1E |      7 |                 9 |   (null) |
| RM001-01 |    1 |          9 |   5 | HK140904-1E |      8 |            (null) |   (null) |

这篇关于Oracle中的递归查询以查找子级和同级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！