主页 / dba / 问题 / 292553
Accepted
PakLui
Asked: 2021-06-01 09:47:40 +0800 CST

我希望通过递归 CTE 查询以树形返回所有汽车，但按每个类别排序

  • 772

这是用于测试的表格和数据：

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');

这是带有递归 CTE 的视图（我尝试过）：

WITH RECURSIVE tree_cars AS (
  select descr
  from cars
  union all

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

但输出不是我所期望的：

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

因为我想要类似的东西：

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

1 个回答

  1. Best Answer

    使用递归 CTE，您可以使用以下查询来实现输出：

    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_

    演示

    • 2

相关问题