mysql - 使用 SUM() 连接多个表

Question

我在 MySQL 中有四个表，例如

T1: division(divisionID, division_name) 
T2: work(workID, divisionID, category, work_desc, value)
T3: approved_work(approve_id, workID, approved_on)
T4: payment(payment_id, workID, amount_paid, paid_date)

现在，我想要一份报告。

T1.division_name, T2.category, T2.work_desc, T2.value, T3.approved_on, sum(amount_paid)

请帮忙SQL查询

Answer 1

试试这个:

SELECT
T1.division_name, 
T2.category, 
T2.work_desc, 
T2.value, 
T3.approved_on, 
sum(T4.amount_paid)
FROM T1 
LEFT JOIN T2 ON T1.division_id=T2.division_id
LEFT JOIN T3 ON T2.work_id=T3.work_id
LEFT JOIN T4 ON T2.work_id=T4.work_id
GROUP BY T1.division_name, 
T2.category, 
T2.work_desc, 
T2.value, 
T3.approved_on;