甲骨文-> Postgres查询 [英] Oracle --> Postgres query

问题描述

我是一个Oracle家伙，试图将这样的查询转换为Postgres.不太了解所有语法，等等.希望有人可以提供帮助吗?

I'm an Oracle guy trying to convert a query like this to Postgres. Not really understanding all the syntax, etc. Was hoping someone can help?

Oracle查询:

Oracle Query:

    SELECT c.code, c.recommendation, s.suggested, s.sugg_by, a.approved, a.app_by
    FROM (SELECT code, recommendation FROM recommendations) c,
         (SELECT code, suggested, sugg_by FROM suggestions) s,
         (SELECT code, approved, app_by FROM suggestions) a
    WHERE c.code = s.code(+)
      AND c.code = a.code(+);
    

非常感谢

推荐答案

您可以尝试使用OUTER JOIN

SELECT c.code, c.recommendation, s.suggested, s.sugg_by, a.approved, a.app_by
FROM (SELECT code, recommendation FROM recommendations) c 
LEFT JOIN (SELECT code, suggested, sugg_by FROM suggestions) s on c.code = s.code
LEFT JOIN (SELECT code, approved, app_by FROM suggestions) a ON c.code = a.code

您似乎不需要使用子查询，因为您在子查询中没有执行任何操作，仅选择了原始列.您可以直接查询该表.

You seem like didn't need to use subquery, because you didn't do anything in the subquery only select original columns. you can query the table directly.

SELECT
    c.code,
    c.recommendation, 
    s.suggested,
    s.sugg_by, 
    a.approved,
    a.app_by
FROM recommendations c 
LEFT JOIN suggestions s on c.code = s.code
LEFT JOIN suggestions a ON c.code = a.code

这篇关于甲骨文-> Postgres查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！