我有两张桌子
>票据：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
