sql - 如何按主 ID 对查询中的行进行排序和分组

Question

我正在尝试在 Oracle 中编写一个选择查询，其中结果按组“隔离”。每个组都有一个父实体，它有一些子实体，所以我想以这样一种方式对行进行分组，每个组都以父实体开头，然后是相应的子实体。

表具有以下结构：

ID | CREATION_DATE       | MASTER_ID
33 | 2019-07-01 09:31:04 | null
52 | 2019-07-01 10:45:04 | null
64 | 2019-07-01 11:00:04 | 33
71 | 2019-07-01 11:01:04 | 52
72 | 2019-07-01 12:31:04 | 33

到目前为止，我尝试了以下代码，它返回未按 MASTER_ID 分组但按 CREATION_DATE 正确排序的行：

SELECT ID, CREATION_DATE, MASTER_ID
FROM ENTITY
WHERE CREATION_DATE >= TO_DATE('06-01-2019','MM-DD-YYYY')
ORDER BY CREATION_DATE DESC

我想找到一种方法来返回以下结构：

ID | CREATION_DATE       | MASTER_ID
33 | 2019-07-01 09:31:04 | null
64 | 2019-07-01 11:00:04 | 33
72 | 2019-07-01 12:31:04 | 33
52 | 2019-07-01 10:45:04 | null
71 | 2019-07-01 11:01:04 | 52

问题是父实体null一直存在，所以我不能使用GROUP BY. 你有什么建议我应该怎么做？

标签： sqloracleoracle11g

Answer 1

您可以通过使用connect by分层查询以及order siblings by子句来执行此操作，例如：

WITH your_table AS (SELECT 33 ID, to_date('2019-07-01 09:31:04', 'yyyy-mm-dd hh24:mi:ss') creation_date, NULL master_id FROM dual UNION ALL
                    SELECT 52 ID, to_date('2019-07-01 10:45:04', 'yyyy-mm-dd hh24:mi:ss') creation_date, NULL master_id FROM dual UNION ALL
                    SELECT 72 ID, to_date('2019-07-01 12:31:04', 'yyyy-mm-dd hh24:mi:ss') creation_date, 33 master_id FROM dual UNION ALL
                    SELECT 64 ID, to_date('2019-07-01 11:00:04', 'yyyy-mm-dd hh24:mi:ss') creation_date, 33 master_id FROM dual UNION ALL
                    SELECT 71 ID, to_date('2019-07-01 11:01:04', 'yyyy-mm-dd hh24:mi:ss') creation_date, 52 master_id FROM dual)
SELECT ID,
       creation_date,
       master_id,
       connect_by_root(ID) top_id
FROM   your_table
START WITH master_id IS NULL
CONNECT BY PRIOR ID = master_id
ORDER SIBLINGS BY creation_date;

        ID CREATION_DATE  MASTER_ID     TOP_ID
---------- ------------- ---------- ----------
        33 01/07/2019 09                    33
        64 01/07/2019 11         33         33
        72 01/07/2019 12         33         33
        52 01/07/2019 10                    52
        71 01/07/2019 11         52         52