mysql - 无法从 Left JoinWhere 和 group by 中找到预期结果

Question

我有两张应收账款表和接收表。

表:应收账款

tranId  roll     month         amount
 1      1111    October-2019    10
 2      1112    October-2019    10
 3      1113    October-2019    10
 4      1114    October-2019    10
 5      1115    October-2019    10

表:接收

tranId   roll       month       amount
1       1111    October-2019    10
2       1111    September-2019  10
3       1113    October-2019    10
4       1114    October-2019    10
5       1115    October-2019    10
6       1116    October-2019    10
7       1117    October-2019    10
8       1118    October-2019    10
9       1119    October-2019    10
10      1120    October-2019    10

在两个表中，月份列都是字符串，并且表接收滚动+月份列是唯一的。我想从这些表格中创建一个应收账款并接收学生的对账单报告(其中 roll=1111)。

预期结果:

month           rcvamount   rcvvmonth      rcvvamount
---------------------------------------------------------
October-2019       10       October-2019    10
---------------------------------------------------------
September-2019     10           -                -

我的查询是:

SELECT receive.month, sum(receive.amount) AS rcvamount, receivable.month AS rcvvmonth,
       receivable.amount AS rcvvamount
FROM receive
    LEFT JOIN receivable ON receive.month = receivable.month
WHERE receive.roll = 1111
GROUP BY receive.month

结果是:

month           rcvamount   rcvvmonth      rcvvamount
----------------|-------|----------------------------------
October-2019    |   50  |       October-2019    10
----------------|-------|----------------------------------
September-2019     10           -                -

接收表中 2019 年 10 月的重新金额应为 10。

Answer 1

您可以在加入之前聚合。或者使用union all并聚合:

SELECT month, SUM(ramount) AS rcvamount, 
       SUM(raamount) AS rcvvamount
FROM ((SELECT month, amount as ramount, null as raamount
       FROM receive
       WHERE roll = 1111
      ) UNION ALL
      (SELECT month, NULL as ramount, amount as raamount
       FROM receiveable
       WHERE roll = 1111
      )
     ) r
GROUP BY month; 

UNION ALL 的一个很好的功能是它可以处理任一表中丢失的数据。