我有一个来自 Oracle+SAS 环境的巨大 SQL 语句。我明白了大部分内容,但最让我困惑的是 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 子句?
Thanks.
尝试这个:
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)