Oracle 右外连接

发布于 2024-11-08 00:02:07 字数 328 浏览 4 评论 0原文

我有一个 oracle sql，在帐户表和付款表之间进行外连接。我想知道仅当付款表中存在匹配记录时如何执行条件 p. payment_status_code = 'R' 。对于其余的情况，不应执行该条件。

FROM
accounts a, payments p
WHERE (a.account_balance <= a.low_balance_level OR a.account_balance <= 0)
AND a.account_id = p.account_id(+)
AND p.payment_status_code = 'R'

问候， -阿南德

原文

I have a oracle sql where I do an outer join between the accounts table and payments table. I want to know how to execute the condition p.payment_status_code = 'R' only when there is a matching record in the payments table. For the rest, the condition should not be executed.

FROM
accounts a, payments p
WHERE (a.account_balance <= a.low_balance_level OR a.account_balance <= 0)
AND a.account_id = p.account_id(+)
AND p.payment_status_code = 'R'

Regards,
-Anand

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

隔岸观火 2024-11-15 00:02:07

FROM
accounts a, payments p
WHERE (a.account_balance <= a.low_balance_level OR a.account_balance <= 0)
AND a.account_id = p.account_id(+)

AND p.payment_status_code(+) = 'R'
                         ^^^

FROM
accounts a, payments p
WHERE (a.account_balance <= a.low_balance_level OR a.account_balance <= 0)
AND a.account_id = p.account_id(+)

AND p.payment_status_code(+) = 'R'
                         ^^^

回复收藏 0 原文

撧情箌佬 2024-11-15 00:02:07

如果我理解正确的话，您只想用 payment_status_code = 'R' 显示结果，对吗？在这种情况下，为什么需要使用外连接？您可以只使用传统的连接。你想做什么？

FROM
accounts a, payments p
WHERE (a.account_balance <= a.low_balance_level OR a.account_balance <= 0)
AND a.account_id = p.account_id
AND p.payment_status_code = 'R'

如果您想显示帐户信息，即使对于那些没有付款的人，您也可以使用左外连接而不是右外连接

FROM
accounts a
left join payments p on a.account_id = p.account_id AND p.payment_status_code = 'R'
WHERE (a.account_balance <= a.low_balance_level OR a.account_balance <= 0)

If I understand you correctly, you only want to show the results with payment_status_code = 'R', right? In that case why would you need to use outer join? You could just use the traditional join. What are you trying to do?

FROM
accounts a, payments p
WHERE (a.account_balance <= a.low_balance_level OR a.account_balance <= 0)
AND a.account_id = p.account_id
AND p.payment_status_code = 'R'

If you want to show the account info, even for those without payment then you would use a left outer join instead of right outer join

FROM
accounts a
left join payments p on a.account_id = p.account_id AND p.payment_status_code = 'R'
WHERE (a.account_balance <= a.low_balance_level OR a.account_balance <= 0)

回复收藏 0 原文

新一帅帅 2024-11-15 00:02:07

FROM
accounts a, payments p
WHERE (a.account_balance <= a.low_balance_level OR a.account_balance <= 0)
AND a.account_id = p.account_id(+)
AND (p.account_id is null or p.payment_status_code = 'R')

FROM
accounts a, payments p
WHERE (a.account_balance <= a.low_balance_level OR a.account_balance <= 0)
AND a.account_id = p.account_id(+)
AND (p.account_id is null or p.payment_status_code = 'R')

回复收藏 0 原文

~没有更多了~