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