PostgreSQL是否有伪列，例如"LEVEL"?在甲骨文?

本文介绍了PostgreSQL是否有伪列，例如"LEVEL"?在甲骨文?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

PostgreSQL在Oracle中是否有伪列，例如"LEVEL"? 如果没有，那么我们如何创建类似于"LEVEL"的列?

Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle? If not, then how can we create a column similar to "LEVEL"?

推荐答案

Postgres没有分层查询.没有CONNECT BY，因此也没有LEVEL.

Postgres does not have hierarchical queries. No CONNECT BY, therefore also no LEVEL.

附加模块tablefunc提供功能 connectby() 几乎相同.参见:

The additional module tablefunc provides the function connectby() doing almost the same. See:

• 什么Oracle的CONNECT BY ... START WITH?等效的PostgreSQL语法是什么?

或者您也可以使用标准递归CTE 和level列，该列随每次递归递增. Oracle中的此查询:

Or you can do similar things with a standard recursive CTE and a level column that's incremented with every recursion. This query in Oracle:

SELECT employee_id, last_name, manager_id, LEVEL FROM employees CONNECT BY PRIOR employee_id = manager_id;

..可以在Postgres中转换为该递归CTE:

.. can be translated to this recursive CTE in Postgres:

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;