在mysql db中，我有people\u表：

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

借钱，借钱，借钱

------------------------------------------
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而不是使用join本身？