postgresql 递归查询，查询父子节点关联关系

postgresql 递归查询，查询父子节点关联关系

CREATE TABLE "public"."sys_department" (
  "id" int4 NOT NULL DEFAULT nextval('sys_department_id_seq'::regclass),
  "name" varchar COLLATE "pg_catalog"."default",
  "pid" int4,
  "order" int4,
  "no" varchar COLLATE "pg_catalog"."default", 
  
  CONSTRAINT "sys_department_pkey" PRIMARY KEY ("id")
);

sql

WITH RECURSIVE CTE(id,name,pid,level) AS (


    SELECT ID,NAME,pid,1 AS Level FROM sys_department WHERE pid=0
    UNION ALL
    SELECT t.id,t.NAME,t.pid,cte.Level+1 AS Level FROM sys_department t
     
		 
		 JOIN CTE ON t.pid=CTE.id
)

select * from cte;