mysql - 如何使用 GROUP BY 两次而不是 JOIN 本身？

Question

在 MYSQL 数据库中，我有 people_table :

------------------------------------------
people_table
------------------------------------------
person_ID(INT)   name(VARCHAR(45))
1                A
2                B
3                C
4                D
------------------------------------------

和 lend_borrow_money_table

------------------------------------------
lend_borrow_money_table
------------------------------------------
bill(DATE) lender_ID  money(INT)  borrower_ID
2018-11-1  1           100        2
2018-11-2  2           200        3
2018-11-3  3           300        4
2018-11-30 2           400        3
------------------------------------------

现在我想要这样选择结果

------------------------------------------
name      lend     borrow   total
A         500      0        500
B         400      500      -100
C         0        600      -600
D         0        300      -300   
------------------------------------------

在我的方式中，我使用 join select 来分组两次但我认为这不是最佳解决方案

SELECT Lender.name,Lend.lend,SUM(money) AS borrow

FROM lend_borrow_money_table
INNER JOIN people_table AS Borrower ON people_table.ID = lend_borrow_money_table.borrower_ID
INNER JOIN
(
    SELECT SUM(money) as lend
    FROM lend_borrow_money_table
    WHERE bill<'2018/11/31' AND bill>'2018/11/1' 
    GROUP BY lender_ID
)AS Lend ON Lend.lender_ID

INNER JOIN people_table AS Lender ON people_table.ID = lend_borrow_money_table.lender_ID

WHERE bill<'2018/12/1' AND bill>'2018/11/1' 

GROUP BY borrower_ID

我的问题是如何使用 GROUP BY 两次而不是 JOIN 本身？

Answer 1

你可以试试下面

select a.name,sum(b.money) as lend, sum(c.money) as borrow, sum(b.money)-sum(c.money) as total
from people_table a
left join lend_borrow_money_table b on a.id=b.lender_ID
left join lend_borrow_money_table c on a.id=c.borrower_ID
where bill>'20181101' and bill<'20181201' 
group by a.name