SQL 选择总和和内连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15040414/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select sum and inner join
提问by Klaus
I have two tables
我有两张桌子
Bills:id amount referenceTransactions:id reference amount
Bills：id amount referenceTransactions：id reference amount
The following SQL query
以下 SQL 查询
SELECT
*,
(SELECT SUM(amount)
FROM transactions
WHERE transactions.reference = bils.reference) AS paid
FROM bills
GROUP BY id HAVING paid<amount
was meant to some rows from table Bills, adding a column paidwith the sum of amount of related transactions.
是为了表中的一些行Bills，添加一列paid包含相关交易金额的总和。
However, it only works when there is at least one transaction for each bill. Otherwise, no line for a transaction-less bill is returned.
但是，它仅在每张账单至少有一笔交易时才有效。否则，不返回无交易账单的行。
Probably, that's because I should have done an inner join!
可能，那是因为我应该做一个内部连接！
So I try the following:
所以我尝试以下操作：
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
However, this returns the same value of paid for all rows! What am I doing wrong ?
但是，这会为所有行返回相同的paid 值！我究竟做错了什么 ？
回答by Guffa
Use a left join instead of a subquery:
使用左连接而不是子查询：
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.amount, b.paid
having b.paid < b.amount
Edit:
To compare the sum of transactions to the amount, handle the null value that you get when there are no transactions:
编辑：
要将交易总和与金额进行比较，请处理没有交易时获得的空值：
having isnull(sum(t.amount), 0) < b.amount
回答by dotNET
You need a RIGHT JOINto include all bill rows.
您需要RIGHT JOIN包含所有帐单行。
EDITSo the final query will be
编辑所以最后的查询将是
SELECT
*,
(SELECT SUM(transactions.amount)
FROM transactions
WHERE transactions.reference = bills.reference) AS paid
FROM bills
WHERE paid < amount
回答by VKS
I knows this thread is old, but I came here today because I encountering the same problem.
我知道这个线程很旧，但我今天来到这里是因为我遇到了同样的问题。
Please see another post with same question: Sum on a left join SQL
请参阅另一篇有相同问题的帖子： Sum on a left join SQL
As the answer says, use GROUP BY on the left table. This way you get all the records out from left table, and it sums the corresponding rows from right table.
正如答案所说，在左表中使用 GROUP BY。通过这种方式，您可以从左表中取出所有记录，并将右表中的相应行相加。
Try to use this:
尝试使用这个：
SELECT
*,
SUM(transactions.sum)
FROM
bills
RIGHT JOIN
transactions
ON
bills.reference = transactions.reference
WHERE
transactions.sum > 0
GROUP BY
bills.id
