Início / dba / Perguntas / 292553
Accepted
PakLui
Asked: 2021-06-01 09:47:40 +0800 CST

Eu gostaria, por meio de CTEs recursivos, uma consulta para retornar todos os carros em forma de árvore, mas classificados em cada categoria

  • 772

Aqui está a tabela e os dados para teste:

CREATE TABLE if not exists cars (
    id serial PRIMARY KEY, 
    parent_code INT,
    descr CHARACTER VARYING(50) NOT NULL
);

-- Level 1
INSERT INTO cars(parent_code, descr) VALUES (null, 'Volkswagen');
INSERT INTO cars(parent_code, descr) VALUES (null, 'Tesla');

-- Level 2
INSERT INTO cars(parent_code, descr) VALUES (1, 'Polo');
INSERT INTO cars(parent_code, descr) VALUES (1, 'Golf');

INSERT INTO cars(parent_code, descr) VALUES (2, '3');
INSERT INTO cars(parent_code, descr) VALUES (2, 'S');
INSERT INTO cars(parent_code, descr) VALUES (2, 'X');

-- Level 3
INSERT INTO cars(parent_code, descr) VALUES (3, 'diesel');
INSERT INTO cars(parent_code, descr) VALUES (3, 'gasoline');

INSERT INTO cars(parent_code, descr) VALUES (4, 'diesel');
INSERT INTO cars(parent_code, descr) VALUES (4, 'gasoline');

INSERT INTO cars(parent_code, descr) VALUES (5, 'electric');
INSERT INTO cars(parent_code, descr) VALUES (6, 'electric');
INSERT INTO cars(parent_code, descr) VALUES (7, 'electric');

E aqui está a visão (que eu tentei) com um CTE recursivo:

WITH RECURSIVE tree_cars AS (
  select descr
  from cars
  union all

  select concat('  -> ', descr)
  from cars
)
--table tree_cars;
select * from tree_cars;

Mas a saída não é o que eu esperava:

Volkswagen
Tesla
Polo
Golf
3
S
X
diesel
gasoline
diesel
gasoline
electric
electric
electric
  -> Volkswagen
  -> Tesla
  -> Polo
  -> Golf
  -> 3
  -> S
  -> X
  -> diesel
  -> gasoline
  -> diesel
  -> gasoline
  -> electric
  -> electric
  -> electric

Já que eu gostaria de algo como:

Tesla
  -> 3
      -> electric
  -> S
      -> electric
  -> X
      -> electric
Volkswagen
  -> Golf
      -> diesel
      -> gasoline
  -> Polo
      -> diesel
      -> gasoline
postgresql cte

1 respostas

  1. Best Answer

    Com o CTE recursivo, você pode usar a consulta abaixo para obter sua saída:

    with recursive cte as (
   select id, descr, parent_code, 1 as lvl_, array[id] as path_
   from cars 
   where parent_code is null
   union all
   select t1.id, lpad('->', t2.lvl_ * 4) || ' ' ||t1.descr, t1.parent_code, t2.lvl_ + 1, t2.path_||t1.id
   from cars t1
     join cte t2 on t1.parent_code = t2.id
)
select descr from cte
order by path_

    DEMO

    • 2

relate perguntas