甲骨文 -->Postgres 查询

我是一名试图将这样的查询转换为 Postgres 的 Oracle 人员.不是很了解所有语法等.希望有人能提供帮助吗?

Oracle 查询:<上一页>选择 c.code、c.recommendation、s.suggested、s.sugg_by、a.approved、a.app_byFROM(选择代码，推荐 FROM 推荐)c，(选择代码，建议，sugg_by FROM 建议) s,(选择代码，已批准，app_by FROM 建议)哪里 c.code = s.code(+)和 c.code = a.code(+);

非常感谢

解决方案

可以尝试使用OUTER JOIN

选择 c.code、c.recommendation、s.suggested、s.sugg_by、a.approved、a.app_byFROM(选择代码，推荐 FROM 推荐)c左连接(选择代码，建议，sugg_by FROM 建议) s on c.code = s.codeLEFT JOIN(选择代码，已批准，app_by FROM 建议)a ON c.code = a.code

您似乎不需要使用子查询，因为您在子查询中没有做任何事情只选择原始列.可以直接查询表.

选择c.代码，c. 推荐，s.建议，s.sugg_by,a.批准，a.app_byFROM 建议 cc.code = s.code 上的 LEFT JOIN 建议LEFT JOIN 建议 a ON c.code = a.code

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 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(+);
    

Much appreciated

解决方案

You can try to use 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