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;更多推荐
PostgreSQL是否有伪列,例如"LEVEL"?在甲骨文?
发布评论