PostgreSQL有类似Oracle中的“LEVEL”伪列吗?
如果没有,那么我们如何创建类似于“LEVEL”的列?
PostgreSQL有类似Oracle中的“LEVEL”伪列吗?
如果没有,那么我们如何创建类似于“LEVEL”的列?
CONNECT BY
,因此也没有LEVEL
。connectby()
几乎实现同样的功能。详情请参见mthorley answer。level
列。SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
..可以在Postgres中使用这个递归CTE进行翻译:
WITH RECURSIVE cte AS (
SELECT employee_id, last_name, manager_id, 1 AS level
FROM employees
UNION ALL
SELECT e.employee_id, e.last_name, e.manager_id, c.level + 1
FROM cte c
JOIN employees e ON e.manager_id = c.employee_id
)
SELECT *
FROM cte;
CREATE EXTENSION IF NOT EXISTS tablefunc;
有关更多信息,请查看文档:
https://www.postgresql.org/docs/current/static/tablefunc.html
这是我们应用程序中一个真实的connectby的例子。我们正在使用它来查找通过其报告树向上汇报给经理的所有人。SELECT system_user.system_user_id
, system_user.first_name
, system_user.last_name
, team.mgr_id as managers_system_user_id
, team.level
, team.hierarchy
FROM connectby('system_user_manager_rltnp'
, 'system_user_id'
, 'system_users_managers_id'
, 2963049 -- the users system_user_id
, 5 -- the max levels of depth
, '~') -- the hierarchy delimiter
AS team(rpt_id numeric, mgr_id numeric, level int, hierarchy text),
system_user
WHERE team.rpt_id = system_user.system_user_id
它将返回以下类似的结果。在此您可以看到级别,以及整个层次结构作为字符串。
"system_user_id","first_name","last_name","managers_system_user_id","level","hierarchy"
"2963049","Debbie","Buswell","",0,"2963049"
"2963045","Linda","Simply","2963049",1,"2963049~2963045"
"2963047","Cindy","Brouillard","2963049",1,"2963049~2963047"
"2963048","Sharon","Burns","2963049",1,"2963049~2963048"
"2963050","Marie-Eve","Casper","2963049",1,"2963049~2963050"
"2963051","Tammy","Cody","2963049",1,"2963049~2963051"
我已经在类似的问题中给出了所有细节。
Stackoverflow Connect By answer
CONNECT BY
对于它可以表达的内容更加清晰简洁,但比WITH RECURSIVE
灵活性要差得多。 - Craig Ringer