主页 / dba / 问题 / 292507
Accepted
tucomax
Asked: 2021-05-31 11:00:48 +0800 CST

如何使用 PostgreSQL 更改层次结构树中的字符串输出？

  • 772

我有一张这样的桌子：

-------------------------------------------------
|  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

我该怎么做？

postgresql cte

1 个回答

  1. Best Answer

    你可以使用这样的东西

    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<>在这里摆弄

    • 4

相关问题