如何制作订购单? [英] How to make ORDER SIBLINGS BY?
本文介绍了如何制作订购单?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用PostgreSQL 9.1.6并尝试构建递归SQL。
我想排序类似于SQL Server中的 ORDER SIBLINGS BY
I'm using PostgreSQL 9.1.6 and trying to build recursive SQL.
I want to sort like ORDER SIBLINGS BY
in SQL-Server does it.
编者注:这可能是指 Oracle ,其中订购单价
实际上存在。
测试表:
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');
查询:
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;
结果:
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
我想对结果集进行如下排序。
I want to sort the result-set up there like below.
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
有什么建议吗?
推荐答案
这将实现您所描述的内容:
This would achieve what you describe:
WITH RECURSIVE t AS (
SELECT emp_id As top_id
, emp_id
, manager_id
, emp_name
FROM recursive_test
WHERE manager_id = 0
UNION ALL
SELECT t.top_id
, a.emp_id
, a.manager_id
, a.emp_name
FROM recursive_test a
JOIN t ON a.manager_id = t.emp_id
)
SELECT emp_id
, manager_id
, emp_name
FROM t
ORDER BY top_id, emp_id;
似乎您想按 emp_id
中学..
WITH RECURSIVE t AS (
SELECT ARRAY[emp_id] AS hierarchy
, emp_id
, manager_id
, emp_name
FROM recursive_test
WHERE manager_id = 0
UNION ALL
SELECT t.hierarchy || a.emp_id
, a.emp_id
, a.manager_id
, a.emp_name
FROM recursive_test a
JOIN t ON a.manager_id = t.emp_id
)
SELECT emp_id
, manager_id
, emp_name
FROM t
ORDER BY hierarchy;
此函数以数组的形式收集祖先(包括自身)并对其进行排序,从而实现像
This one collects ancestors (including self) in an array and sorts by it, achieving an ordering like in a table of contents.
按数组排序按预期工作。相关:
Sorting by an array works as expected. Related:
- How to ORDER BY typical software release versions like X.Y.Z?
这篇关于如何制作订购单?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文