sql – 选择sum和inner join

>票据：id金额参考
>交易：id参考金额

以下SQL查询

SELECT 
   *,(SELECT SUM(amount) 
    FROM transactions 
    WHERE transactions.reference = bils.reference) AS paid
FROM bills
GROUP BY id HAVING paid<amount

是表格Bills中的某些行,添加了一个用相关交易金额总和支付的列.

但是,它仅在每个账单至少有一笔交易时才有效.否则,不返回无交易账单的行.

也许,那是因为我应该做一个内部联接！

所以我尝试以下方法：

SELECT 
   *,(SELECT SUM(transactions.amount) 
    FROM transactions 
    INNER JOIN bills ON transactions.reference = bills.reference) AS paid
FROM bills
GROUP BY id 
HAVING paid < amount

但是,这会返回所有行的相同付费值！我究竟做错了什么 ？

解决方法

select b.id,b.amount,b.paid,sum(t.amount) as transactionamount
from bills b
left join transactions t on t.reference = b.reference
group by b.id,b.paid
having b.paid < b.amount

编辑：
要将事务总和与金额进行比较,请处理没有事务时获得的空值：

having isnull(sum(t.amount),0) < b.amount

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!