PostgreSQL - 在 oracle 中使用“start with”和“connect by”迁移查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24038950/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - migrate a query with 'start with' and 'connect by' in oracle
提问by vinod kumar
I have the following query in oracle. I want to convert it to PostgreSQL form. Could someone help me out in this,
我在 oracle 中有以下查询。我想将其转换为 PostgreSQL 形式。有人可以帮我解决这个问题吗
SELECT user_id, user_name, reports_to, position
FROM pr_operators
START WITH reports_to = 'dpercival'
CONNECT BY PRIOR user_id = reports_to;
回答by vyegorov
A something like this should work for you (SQL Fiddle):
像这样的东西应该适合你（SQL Fiddle）：
WITH RECURSIVE q AS (
SELECT po.user_id,po.user_name,po.reports_to,po.position
FROM pr_operators po
WHERE po.reports_to = 'dpercival'
UNION ALL
SELECT po.user_id,po.user_name,po.reports_to,po.position
FROM pr_operators po
JOIN q ON q.user_id=po.reports_to
)
SELECT * FROM q;
You can read more on recursive CTE's in the docs.
Note: your design looks strange -- reports_tocontains string literals, yet it is being comapred with user_idwhich typicaly is of type integer.
注意：您的设计看起来很奇怪——reports_to包含字符串文字，但它正在与user_id典型的类型进行比较integer。
