选择记录的父级 [英] SELECT the parent of a record

问题描述

我有这个要求，我需要在select语句中附加一个新列.它保存父级的disp_order.

I have this requirement that I need to attach a new column in select statement. It holds the disp_order of the parent level.

我目前有这个sql语句

I currently have this sql statement

SELECT DISTINCT order_no, 
                code, 
                disp_order, 
                lvl, 
                description 
           FROM tbl_pattern

+----------+------+------------+-----+-------------+
| order_no | code | disp_order | lvl | description |
+----------+------+------------+-----+-------------+
| RM001-01 | 1    | 0          | 1   | HK140904-1A |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |
| RM001-01 | 1    | 2          | 3   | HK140904-1B |
| RM001-01 | 1    | 3          | 4   | HK140904-1C |
| RM001-01 | 1    | 4          | 5   | HK140904-1D |
| RM001-01 | 1    | 5          | 2   | HK140904-1E |
| RM001-01 | 1    | 6          | 3   | HK140904-1E |
| RM001-01 | 1    | 7          | 3   | HK140904-1X |
| RM001-01 | 1    | 8          | 4   | HK140904-1E |
| RM001-01 | 1    | 9          | 5   | HK140904-1E |
+----------+------+------------+-----+-------------+

父列在表上不存在，但是我想使该级别的disp_order高于当前记录.

parent column does not exist on table, but I want to get the disp_order of the level higher than the current record.

在示例表中，结果应如下所示:

From the example table, the result should be like this:

+----------+------+------------+-----+-------------+--------+
| order_no | code | disp_order | lvl | description | parent |
+----------+------+------------+-----+-------------+--------+
| RM001-01 | 1    | 0          | 1   | HK140904-1A |        |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |    0   |
| RM001-01 | 1    | 2          | 3   | HK140904-1B |    1   |
| RM001-01 | 1    | 3          | 4   | HK140904-1C |    2   |
| RM001-01 | 1    | 4          | 5   | HK140904-1D |    3   |
| RM001-01 | 1    | 5          | 2   | HK140904-1E |    0   |
| RM001-01 | 1    | 6          | 3   | HK140904-1E |    5   |
| RM001-01 | 1    | 7          | 3   | HK140904-1X |    5   |
| RM001-01 | 1    | 8          | 4   | HK140904-1E |    7   |
| RM001-01 | 1    | 9          | 5   | HK140904-1E |    8   |
+----------+------+------------+-----+-------------+--------+

通过提供的示例层次结构进行数据表示:

Data representation through hierarchy of sample provided:

1
  └2
    └3
      └4
        └5
  └2
    └3
    └3
      └4
        └5

推荐答案

您可以使用相关子查询来查找上一级的最新disp_order:

You can use a correlated sub-query to find the most recent disp_order for the previous level:

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 :

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

结果 :

Results:

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

这篇关于选择记录的父级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！