如何有条件地从两个表之一中选择一个字段? [英] How do I conditionally select a field from one of two tables?

问题描述

我有两个表的发票和未付款项，它们都有以下共同的行:invoice_id和balance.我想在MySQL中进行选择，这样就可以了:

I have two tables invoices and pending_payments both of which have the following rows in common: invoice_id and balance. I want to do a select in MySQL that'll work thus:

[伪代码]

if(invoice_id exists in pending_payments table) {
    select balance from pending_payments where invoice_id = yadayadayada
} else {
    select balance from invoices where invoice_id = yadayadayada
}

这在MySQL中甚至可行吗?如果可以，怎么办?

Is this even doable in MySQL? If so, how?

推荐答案

select i.invoice_id, coalesce(pp.balance, i.balance) as Balance
from invoices i
left outer join pending_payments pp on i.invoice_id = pp.invoice_id

让我知道对于同一invoice_id，在pending_payments中是否可以有多行，我将提出另一种解决方案.

Let me know if there can be multiple rows in pending_payments for the same invoice_id and I will propose an alternate solution.

这篇关于如何有条件地从两个表之一中选择一个字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！