oracle 中的level,PostgreSQL在Oracle中是否有像“LEVEL”这样的伪列？

是的，Postgres支持像Oracle这样的“LEVEL”。

但是，正如其他答案指出的那样，您必须已加载表扩展扩展。

如果你有你的Postgres数据库管理员权限，你可以用这个加载：

CREATE EXTENSION IF NOT EXISTS tablefunc;

有关更多信息检查文档

下面是一个真实生活中的例子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"