Início / dba / Perguntas / 292507
Accepted
tucomax
Asked: 2021-05-31 11:00:48 +0800 CST

Como posso alterar a saída de string em uma árvore de hierarquia com o PostgreSQL?

  • 772

Tenho uma tabela assim:

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

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

Eu preciso gerar esse tipo de saída:

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

Estou trabalhando no PostgreSQL. Eu tenho tentado isso com um CTE recursivo, mas não consegui gerar o que gosto:

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;

Esse CTE recursivo produz a seguinte saída indesejável:

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

Como eu posso fazer isso?

postgresql cte

1 respostas

  1. Best Answer

    Você poderia usar algo assim

    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

      identificação | lista_pais                    
---: | :-----------------------------
   2 | Testes de laboratório                      
1900 | --> Exames de sangue           
2044 | --> Cálcio               
   1 | Radiologia                      
   4 | --> Ressonância             
   3 | --> Radiologia Normal      
1100 | --> Ressonância Cerebral

    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;

      identificação | lista_pais                                   
---: | :---------------------------------------------
   2 | Testes de laboratório                                     
1900 | Exames de laboratório --> Exames de sangue                     
2044 | Testes de laboratório --> Cálcio                         
   1 | Radiologia                                     
   3 | Radiologia --> Radiologia Normal                
   4 | Radiologia --> Ressonância                       
1100 | Radiologia --> Ressonância --> Ressonância Cerebral

    db<>fique aqui

    • 4

relate perguntas