如何以擴展的樹狀方式對遞歸查詢的結果進行排序？

假設您有這樣的nodes表：

CREATE TABLE nodes (
   node serial PRIMARY KEY,
   parent integer NULL REFERENCES nodes(node),
   ts timestamp NOT NULL DEFAULT now()
);

它表示一個標準的類似節點的樹結構，頂部有根節點，有幾個子節點懸掛在根節點或其他子節點上。

讓我們插入幾個範例值：

INSERT INTO nodes (parent) VALUES
 (NULL), (NULL), (NULL), (NULL)
, (1), (1), (1), (1), (6), (1), (6)
, (9), (6), (6), (3), (3), (3), (15);

現在我想檢索前 10 個根節點及其深度為 4 的所有子節點：

WITH RECURSIVE node_rec AS (
   (SELECT 1 AS depth, * FROM nodes WHERE parent IS NULL LIMIT 10)

   UNION ALL

   SELECT depth + 1, n.*
   FROM nodes AS n JOIN node_rec ON (n.parent = node_rec.node)
   WHERE depth < 4
)
SELECT * FROM node_rec;

這很好用，並給了我以下結果：

depth | node | parent 
-------+------+--------
    1 |  1   |
    1 |  2   |
    1 |  3   |
    1 |  4   |
    2 |  5   |  1
    2 |  6   |  1
    2 |  7   |  1
    2 |  8   |  1
    2 | 10   |  1
    2 | 15   |  3
    2 | 16   |  3
    2 | 17   |  3
    3 |  9   |  6
    3 | 11   |  6
    3 | 13   |  6
    3 | 14   |  6
    3 | 18   | 15
    4 | 12   |  9

您可能已經註意到，沒有ORDER BY子句，因此沒有定義順序。您在此處看到的順序是從根節點到更深的節點。

從下面的範例圖片中可以看出，我將如何對結果進行排序，因為它們會出現在展開的樹視圖中？

我基本上希望將子節點放置在其相應的父節點之後。如果兩個或多個子節點具有相同的父節點，我希望它們按時間戳排序。根據上面的範例，這是我想要實現的所需輸出順序：

depth | node | parent | ts
-------+------+--------+---------
    1 |  1   |        | 2014-01-01 00:00:00
    2 |  5   |     1  | 2014-01-01 00:10:00
    2 |  6   |     1  | 2014-01-01 00:20:00
    3 |  9   |     6  | 2014-01-01 00:25:00
    4 |  12  |     9  | 2014-01-01 00:27:00
    3 |  11  |     6  | 2014-01-01 00:26:00
    3 |  13  |     6  | 2014-01-01 00:30:00
    3 |  14  |     6  | 2014-01-01 00:36:00
    2 |  7   |     1  | 2014-01-01 00:21:00
    2 |  8   |     1  | 2014-01-01 00:22:00
    2 |  10  |     1  | 2014-01-01 00:23:00
    1 |  2   |        | 2014-01-01 00:08:00
    1 |  3   |        | 2014-01-01 00:09:00
    2 |  15  |     3  | 2014-01-01 10:00:00
    3 |  18  |     15 | 2014-01-01 11:05:00
    2 |  16  |     3  | 2014-01-01 11:00:00
    2 |  17  |     3  | 2014-01-01 12:00:00
    1 |  4   |        | 2014-01-01 00:10:00

您可以按表示從根到葉的路徑的數組進行排序：

為了簡單起見，我省略了LIMIT（所以我們也不需要額外的括號）和WHERE您的原始查詢。這些可以自由添加。

基本查詢

WITH RECURSIVE node_rec AS (
  SELECT 1 AS depth, *
       , ARRAY[node] AS path
  FROM   nodes
  WHERE  parent IS NULL

  UNION ALL
  SELECT r.depth + 1, n.*
       , r.path || n.node
  FROM   node_rec r 
  JOIN   nodes    n ON n.parent = r.node
  )
SELECT *
FROM   node_rec
ORDER  BY path;

Postgres 14或更高版本中的專用SEARCH子句可以簡化相同的操作：

WITH RECURSIVE node_rec AS (
  SELECT 1 AS depth, *
  FROM   nodes
  WHERE  parent IS NULL

  UNION ALL
  SELECT r.depth + 1, n.*
  FROM   node_rec r 
  JOIN   nodes    n ON n.parent = r.node
  ) SEARCH DEPTH FIRST BY node SET path
SELECT *
FROM   node_rec
ORDER  BY path, ts;

按附加列排序

如果兩個或多個子節點具有相同的父節點，我希望它們按時間戳排序。

我們確實需要ts在每個級別上首先按時間戳列排序，並且node只是一個決勝局（時間戳可能不是唯一的）。最簡單的解決方案是將兩者都作為record類型：

WITH RECURSIVE node_rec AS (
  SELECT 1 AS depth, *
        , ARRAY[(ts, node)] AS path
  FROM   nodes
  WHERE  parent IS NULL

  UNION ALL
  SELECT r.depth + 1, n.*
       , r.path || (n.ts, n.node)
  FROM   node_rec r 
  JOIN   nodes    n ON n.parent = r.node
  )
SELECT *
FROM   node_rec
ORDER  BY path;

使用Postgres 14或更高版本中的SEARCH子句進行等效、更簡單的查詢：

WITH RECURSIVE node_rec AS (
  SELECT 1 AS depth, *
  FROM   nodes
  WHERE  parent IS NULL

  UNION ALL
  SELECT r.depth + 1, n.*
  FROM   node_rec r 
  JOIN   nodes    n ON n.parent = r.node
  ) SEARCH DEPTH FIRST BY ts, node SET path
SELECT *
FROM   node_rec
ORDER  BY path, ts;

如您所見，新語法允許為排序指定多個列。

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/63153