将Oracle SQL Select转换为PostgreSQL select

本文介绍了将Oracle SQL Select转换为PostgreSQL select的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下SQL语句:

SELECT ABX.ABX_APO_NUMBER, COUNT(A1.PROCESS_MODE) AS NUM_PLANNING, COUNT(A2.PROCESS_MODE) AS NUM_SETUP, COUNT(A3.PROCESS_MODE) AS NUM_OUTPUT FROM ABX, USER_INSTANCE U, ACTIVE_PROCESS A1, ACTIVE_PROCESS A2, ACTIVE_PROCESS A3 WHERE U.ABX_APO_NUMBER (+) = ABX.ABX_APO_NUMBER AND A1.PROCESS_INSTANCE_NUMBER (+) = U.INSTANCE_NUMBER AND A1.PROCESS_MODE (+)= 'PLANNING' AND A2.PROCESS_INSTANCE_NUMBER (+) = U.INSTANCE_NUMBER AND A2.PROCESS_MODE (+) = 'SETUP' AND A3.PROCESS_INSTANCE_NUMBER (+) = U.INSTANCE_NUMBER AND A3.PROCESS_MODE (+) = 'OUTPUT' GROUP BY ABX.ABX_APO_NUMBER

(+)导致适合PG ...我可以只删除它们，这意味着同一件事吗? Postgres会理解的等效内容是什么?

The (+) is causing fits for PG... Can I just remove those and it will mean the same thing? What would be the equivalent that Postgres would understand?

推荐答案

以下是使用ANSI-92 JOIN语法重新编写的查询:

Here is your query re-written using ANSI-92 JOIN syntax:

SELECT a.abx_apo_number, COUNT(ap1.process_mode) AS NUM_PLANNING, COUNT(ap2.process_mode) AS NUM_SETUP, COUNT(ap3.process_mode) AS NUM_OUTPUT FROM ABX a LEFT JOIN USER_INSTANCE u ON u.abx_apo_number = a.abx_apo_number LEFT JOIN ACTIVE_PROCESS ap1 ON ap1.process_instance_number = u.instance_number AND ap1.process_mode = 'PLANNING' LEFT JOIN ACTIVE_PROCESS ap2 ON ap2.process_instance_number = u.instance_number AND ap2.process_mode = 'SETUP' LEFT JOIN ACTIVE_PROCESS ap3 ON ap3.process_instance_number = u.instance_number AND ap3.process_mode = 'OUTPUT' GROUP BY a.abx_apo_number

(+)是Oracle特定的LEFT OUTER JOIN语法.要删除它，将要求每个USER_INSTANCE.instance_number都必须具有以下三种过程模式的值:PLANNING，SETUP和OUTPUT-省略一个，并且abx_apo_number不会显示在输出中.

The (+) is Oracle specific LEFT OUTER JOIN syntax. To remove it, would require that each USER_INSTANCE.instance_number would have to have values for the process modes being all three: PLANNING, SETUP, and OUTPUT - omit one, and the abx_apo_number would not be displayed in the output.