一对多内部联接

Question

所以，我的问题是。我有餐桌付款和餐桌费用，所以，一次付款有很多费用，但一次收费有一次付款。我需要选择所有付款，其中一次付款的所有费用金额！=一次付款的所有费用。

代码示例

select payment_account, payment_agreement, payment_pay, payment_charge
from payment join
     charge
     on payment_id = charge_payment_id
where charge_summ != payment_charge
group by payment_id;

Answer 1

表结构在任何情况下都很有用，请尝试以下查询：

select payment_account, payment_agreement, payment_pay, payment_charge
from payment join
(select charge_payment_id, sum(payment_charge) as totally from charge group by 
charge_payment_id) A 
on payment_id = charge_payment_id and payment_charge != totally;

在这里，您首先对费用求和并与结果进行比较。

Answer 2

也许你可以在没有连接的情况下做到这一点，只需使用 sub select 来获取payment_charge的总和并将其与每笔付款的charge_summ进行比较：

select payment_account, payment_agreement, payment_pay, payment_charge
from payment 
where payment.charge_summ <> (select sum(payment_charge)
                        from charge 
                        where payment.payment_id = charge_payment_id)

编辑您可以使用 JOIN 和无需内部选择来执行此操作：

select payment_id,payment_account, payment_agreement, payment_pay, charge_summ,sum(payment_charge) 
from payment join
     charge
     on payment_id = charge_payment_id
group by payment_id,payment_account, payment_agreement, payment_pay,charge_summ
having charge_summ <> sum(payment_charge)