如何使用 PostgreSQL 更改層次結構樹中的字元串輸出？

我有一張這樣的桌子：

-------------------------------------------------
|  id  | description         | parent_id   |  cost
--------------------------------------------------
| 1    |  Radiology         |       NULL  | 0.00
| 2    |  Lab Tests         |       NULL  | 0.00
| 3    |  Normal Radiology  |         1   | 0.00
| 4    |  Resonance         |         1   | 100.00
| 1100 |  Cerebral Resonance|         4   | 200.00
| 1900 |  Blood Tests       |         2   | 10.00
| 2044 |  Calcium           |         2   | 50.00

---------------------------------------------------

我需要生成這種輸出：

Radiology
  -->Normal Radiology
  -->Resonance
     -->Cerebral Resonance with contrast
Lab Test
   --> Blood Test
   --> Calcium

我正在研究 PostgreSQL。我一直在嘗試使用遞歸 CTE，但無法生成我喜歡的內容：

WITH RECURSIVE hierarchy AS (
   SELECT  id, CAST(description AS TEXT) AS parent_list
   FROM    orders
   WHERE   parent_id is null
     UNION
   SELECT  c.id,
           CAST(c2.parent_list || ' --> ' || c.description as text) as parent_list
   FROM orders c
   INNER JOIN hierarchy c2 ON c.parent_id = c2.id )

SELECT  id, parent_list
FROM     hierarchy
GROUP BY id, parent_list
ORDER BY parent_list;

該遞歸 CTE 會產生以下不受歡迎的輸出：

Radiology
Radiology--> Normal Radiology
Radiology--> Resonance
Radiology--> Resonance --> Cerebral Resonance with contrast
Lab Test
Lab Test --> Blood Test
Lab Test --> Calcium

我該怎麼做？

你可以使用這樣的東西

CREATE TABLE orders (
  "id" INTEGER,
  "description" VARCHAR(18),
  "parent_id" VARCHAR(4),
  "cost" DECIMAL(8,2)
);

INSERT INTO orders
  ("id", "description", "parent_id", "cost")
VALUES
  ('1', 'Radiology', NULL, '0.00'),
  ('2', 'Lab Tests', NULL, '0.00'),
  ('3', 'Normal Radiology', '1', '0.00'),
  ('4', 'Resonance', '1', '100.00'),
  ('1100', 'Cerebral Resonance', '4', '200.00'),
  ('1900', 'Blood Tests', '2', '10.00'),
  ('2044', 'Calcium', '2', '50.00');

WITH RECURSIVE hierarchy AS (
    SELECT  id, 1 AS rown, CAST(description AS TEXT) AS parent_list, id as parent
    FROM    orders
    WHERE   parent_id is null
    UNION  
    SELECT  c.id
    ,rown + 1 as rown
    ,CAST(repeat('    ', rown) || ' --> ' || c.description as text) as parent_list
    ,parent
    FROM orders c
    INNER JOIN hierarchy c2 ON CAST(c.parent_id AS INTEGER) = c2.id )
SELECT id,parent_list FROM hierarchy
ORDER BY parent DESC,rown

 編號 | 父列表 
---: | :------------------------------
 2 | 實驗室測試 
1900 | --> 驗血 
2044 | --> 鈣 
 1 | 放射學 
 4 | --> 共振 
 3 | --> 普通放射學 
1100 | --> 大腦共振

WITH RECURSIVE hierarchy AS (
    SELECT  id, CAST(description AS TEXT) AS parent_list, 1 AS rown, id as parent
    FROM    orders
    WHERE   parent_id is null
    UNION  
    SELECT  c.id
    ,CAST(c2.parent_list || ' --> ' || c.description as text) as parent_list
    ,rown + 1 as rwon
    ,parent
    FROM orders c
    INNER JOIN hierarchy c2 ON CAST(c.parent_id AS INTEGER) = c2.id )
SELECT  id, parent_list
FROM    hierarchy
GROUP BY id, parent_list
ORDER BY parent_list;

 編號 | 父列表 
---: | :---------------------------------------------
 2 | 實驗室測試 
1900 | 實驗室測試 --> 血液測試 
2044 | 實驗室測試 --> 鈣 
 1 | 放射學 
 3 | 放射學 --> 普通放射學 
 4 | 放射學 --> 共振 
1100 | 放射學 --> 共振 --> 腦共振

db<>在這裡擺弄

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