CREATE TABLE DEPARTMENT ( ID INTEGER PRIMARY KEY, NAME VARCHAR(32), PARENT_ID INTEGER REFERENCES DEPARTMENT(ID) );下面我们造几条测试数据 INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(1, 'DEPARTMENT_1', NULL); INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(11, 'DEPARTMENT_11', 1); INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(12, 'DEPARTMENT_12', 1); INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(111, 'DEPARTMENT_111', 11); INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(121, 'DEPARTMENT_121', 12); INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(122, 'DEPARTMENT_122', 12);其中 - DEPARTMENT_1是顶级节点,它有两个子节点DEPARTMENT_11和DEPARTMENT_12。 - DEPARTMENT_11节点又有一个子节点DEPARTMENT_111。 - DEPARTMENT_12节点有两个子节点DEPARTMENT_121和DEPARTMENT_122。 下面是递归查询生成树状结构查询语句 WITH RECURSIVE T (ID, NAME, PARENT_ID, PATH, DEPTH) AS ( SELECT ID, NAME, PARENT_ID, ARRAY[ID] AS PATH, 1 AS DEPTH FROM DEPARTMENT WHERE PARENT_ID IS NULL UNION ALL SELECT D.ID, D.NAME, D.PARENT_ID, T.PATH || D.ID, T.DEPTH + 1 AS DEPTH FROM DEPARTMENT D JOIN T ON D.PARENT_ID = T.ID ) SELECT ID, NAME, PARENT_ID, PATH, DEPTH FROM T ORDER BY PATH; ID NAME PARENT_ID PATH DEPTH 1 DEPARTMENT_1 1 1 11 DEPARTMENT_11 1 1,11 2 111 DEPARTMENT_111 11 1,11,111 3 12 DEPARTMENT_12 1 1,12 2 121 DEPARTMENT_121 12 1,12,121 3 122 DEPARTMENT_122 12 1,12,122 3原文:http://blog.csdn.net/kongxx/article/details/47035491 转载请保留固定链接: https://linuxeye.com/database/2742.html |