我正在使用 PostgreSQL 9.1.6 并尝试构建递归 SQL。
我想排序ORDER SIBLINGS BY
在 SQL-Server 中就是这样做的。
Editor's note: This is probably supposed to refer to Oracle, where ORDER SIBLINGS BY actually exists.
测试表:
create table RECURSIVE_TEST(
EMP_ID int,
MANAGER_ID int,
EMP_NAME varchar(30)
);
insert into recursive_test values
(1 ,0 ,'MANAGER1'),
(2 ,0 ,'MANAGER2'),
(3 ,0 ,'MANAGER3'),
(4 ,0 ,'MANAGER4'),
(5 ,1 ,'emp1'),
(6 ,3 ,'emp2'),
(7 ,4 ,'emp3'),
(8 ,2 ,'emp4'),
(9 ,2 ,'emp5'),
(10 ,3 ,'emp6'),
(11 ,4 ,'emp7'),
(12 ,3 ,'emp8'),
(13 ,4 ,'emp9'),
(14 ,2 ,'emp10'),
(15 ,2 ,'emp11'),
(16 ,1 ,'emp12');
Query:
WITH RECURSIVE T AS
(
SELECT A.EMP_ID
,A.MANAGER_ID
,A.EMP_NAME
FROM RECURSIVE_TEST A
WHERE MANAGER_ID = 0
UNION ALL
SELECT A.EMP_ID
,A.MANAGER_ID
,A.EMP_NAME
FROM RECURSIVE_TEST A, T
WHERE A.MANAGER_ID = T.EMP_ID
)
SELECT * FROM T;
Result:
emp_id | manager_id | emp_name
--------+------------+----------
1 | 0 | MANAGER1
2 | 0 | MANAGER2
3 | 0 | MANAGER3
4 | 0 | MANAGER4
5 | 1 | emp1
6 | 3 | emp2
7 | 4 | emp3
8 | 2 | emp4
9 | 2 | emp5
10 | 3 | emp6
11 | 4 | emp7
12 | 3 | emp8
13 | 4 | emp9
14 | 2 | emp10
15 | 2 | emp11
16 | 1 | emp12
我想对结果集进行排序,如下所示。
emp_id | manager_id | emp_name
--------+------------+----------
1 | 0 | MANAGER1
5 | 1 | emp1
16 | 1 | emp12
2 | 0 | MANAGER2
8 | 2 | emp4
9 | 2 | emp5
14 | 2 | emp10
15 | 2 | emp11
3 | 0 | MANAGER3
6 | 3 | emp2
10 | 3 | emp6
12 | 3 | emp8
4 | 0 | MANAGER4
7 | 4 | emp3
11 | 4 | emp7
13 | 4 | emp9
有什么建议吗?