SQL：行不均匀的表的总和

I have this sql statement:

我有这个sql语句：

SELECT
payment.receipt AS or_no,
payment.payment_dt,
payment.vno,
concat(p.family_name, ', ', p.given_name, ' ', p.middle_name, COALESCE(', ' || p.suffix, '')) AS name,
$$English's Special$$ AS department,
SUM(payment.amount) AS income,
SUM(discount.amount) AS discount,
0 AS other_discount,
0 AS tax
FROM payment_tbl payment
LEFT JOIN person p ON payment.person_id = p.id
INNER JOIN discount_tbl discount on payment.vno = discount.vno 
GROUP BY or_no, name, payment_dt, payment.vno, payment.amount

UNION ALL 

...


SELECT * FROM payment_tbl:
receipt | amount | vno
1       | 5000   | V2016197

SELECT * FROM discount_tbl;
id | vno       | gross | amount (20% discount)
1  | V2016197  | 0     | 0
2  | V2016197  | 0     | 0
3  | V2016197  | 3000  | 600

If I do the select * from payment_tbl inner join discount_tbl, the result would be 3 rows, thus the SUM(payment.amount) would be 15000 instead of 5000.

如果我从payment_tbl内连接discount_tbl执行select *，结果将是3行，因此SUM（payment.amount）将是15000而不是5000。

How can I join the two tables?

我如何加入这两张桌子？

3 个解决方案

#1

My suggestion ? Use a correlated query to sum the amount from discount_tbl , then it won't produce duplicate records:

我的建议？使用相关查询来对discount_tbl中的金额求和，然后它不会产生重复记录：

SELECT
payment.receipt AS or_no,
payment.payment_dt,
payment.vno,
concat(p.family_name, ', ', p.given_name, ' ', .middle_name, COALESCE(', ' || p.suffix, '')) AS name,
$$English's Special$$ AS department,
SUM(payment.amount) AS income,
(SELECT SUM(d.amount) FROM discount_tbl d
 WHERE d.vno = payment.vno) as discount ,
0 AS other_discount,
0 AS tax
FROM payment_tbl payment
LEFT JOIN person p ON payment.person_id = p.id
GROUP BY or_no, name, payment_dt, payment.vno, payment.amount

Btw, in the CONCAT() you are missing the alias in .middle_name , typo?

顺便说一句，在CONCAT（）中你错过了.middle_name中的别名，错字？

#2

select (select sum(amount) from payment_tbl a where a.vno = b.vno) as amount 
from discount_tbl b

Try this query. Instead of join use a subquery to get amount.

试试这个查询。而不是使用子查询来获取金额。

#3

You should SUM your discounts on the discount_tbl table and use the produced table in the JOIN

您应该在discount_tbl表上提供折扣，并在JOIN中使用生成的表

.
.
SUM(payment.amount) AS income,
discount.amount AS discount,
0 AS other_discount,
0 AS tax
FROM payment_tbl payment
LEFT JOIN person p ON payment.person_id = p.id
INNER JOIN ( SELECT vno,SUM(amount) amount FROM discount_tbl GROUP BY vno ) discount 
on payment.vno = discount.vno 
.
.

#1