sql - Oracle 中的复杂左外连接，转换为 PostgreSQL

Question

我有这个巨大的 SQL 语句，来自 Oracle+SAS 环境。我得到了大部分，但最让我困惑的是 WHERE 子句中的左外连接/加号。我需要将其转换为 Postgres。我可以处理代码的第一部分，但让我感到困惑的是联接。

SELECT
--A bunch of columns from several tables
FROM prd_acct_cmp_grp pacg,
    product_acct pa,
    customer_acct ca,
    (SELECT DISTINCT member_id, group_id
     FROM group_members
     WHERE group_id IN (33158, 27156, 35376, 36217)) gm,
    prd_acct_acct_cmp pac,
    pacg_usage pu,
    sales_hierarchy sh,
    sales_region sr
WHERE pacg.component_group_cd = 'AN'
  AND pacg.component_grp_val IN (%s) --string that is added in later
  AND pacg.product_account_id = pa.product_account_id
  AND pa.customer_acct_id = ca.customer_acct_id
  AND ca.customer_acct_id = gm.member_id(+) 
  AND pacg.product_account_id = pac.product_account_id
  AND pacg.occurencce_number = pac.occurence_number
  AND pac.prcmp_code = 'USAGE'
  AND pacg.component_group_cd = pu.component_group_cd(+) 
  AND pacg.component_grp_val = pu.component_grp_val(+) 
  AND ca.primary_sales_rep = sh.sales_rep_id(+) 
  AND sh.region_cd = sr.sales_region_code(+) 

我知道从 Oracle 转换时如何进行简单连接，但是，这个有多个相同表的实例正在比较连接，混合了许多不需要连接的条件。那么如何连接呢？我是否需要在语句末尾添加一个 WHERE 子句？

谢谢。

Answer 1

试试这个:

SELECT
--A bunch of columns from several tables
FROM prd_acct_cmp_grp pacg
JOIN product_acct pa
  ON pacg.product_account_id = pa.product_account_id
JOIN customer_acct ca
  ON pa.customer_acct_id = ca.customer_acct_id
JOIN prd_acct_acct_cmp pac
  ON pacg.product_account_id = pac.product_account_id
 AND pacg.occurencce_number = pac.occurence_number
 AND pac.prcmp_code = 'USAGE'
LEFT JOIN (SELECT DISTINCT member_id, group_id
           FROM group_members
           WHERE group_id IN (33158, 27156, 35376, 36217)) gm
  ON ca.customer_acct_id = gm.member_id
LEFT JOIN sales_hierarchy sh
  ON ca.primary_sales_rep = sh.sales_rep_id
LEFT JOIN sales_region sr
  ON sh.region_cd = sr.sales_region_code
LEFT JOIN pacg_usage pu  
  ON pacg.component_group_cd = pu.component_group_cd
 AND pacg.component_grp_val = pu.component_grp_val
WHERE pacg.component_group_cd = 'AN'
  AND pacg.component_grp_val IN (%s) --string that is added in later