MYSQL group by in subquery 但需要所有数据

我有两个表，一个是transactions，另一个是expenses

交易表:

id  amount  created 
1   300     2019-10-01 00:00:00 
2   200     2019-11-01 00:00:00 
3   230     2019-11-13 00:00:00 
4   130     2019-11-13 00:00:00 

费用表:

id  amount  created 
1   600     2019-11-13 00:00:00 

两个表的总和，我在下面写了查询，这对我来说工作正常。

(SELECT IFNULL(date(t1.created), date(ex.created)) as Date , sum(t1.amount) as ReceiveAmount,ex.amount as ExpensesAmount  
    FROM transactions as t1 
    LEFT JOIN ( 
        SELECT sum(e.amount) as amount, created 
           FROM expenses as e  
           group by date(e.created) 
        ) as ex 
    ON date(ex.created) =  date(t1.created) 
    GROUP BY date(t1.created)) 
 
    UNION 
 
    (SELECT IFNULL(date(t1.created), date(ex.created)) as Date, sum(t1.amount) as Receive,ex.amount as ExpensesAmount  
    FROM transactions as t1 
 
    RIGHT JOIN ( 
        SELECT sum(e.amount) as amount, created 
        FROM expenses as e  
        group by date(e.created) 
    ) as ex 
    ON date(t1.created) = date(ex.created) 
    GROUP BY date(t1.created)) ORDER BY Date 

输出:

Date        ReceiveAmount   ExpensesAmount   
2019-10-01  300             NULL 
2019-11-01  200             NULL 
2019-11-13  360             600 

但是现在如果我想从两个表中获取所有金额而不求和。我遗漏了日期 2019-11-13 的一笔交易。

(SELECT IFNULL(date(t1.created), date(ex.created)) as Date , t1.amount as ReceiveAmount,ex.amount as ExpensesAmount  
FROM transactions as t1 
LEFT JOIN ( 
    SELECT e.amount as amount, created 
       FROM expenses as e  
       group by date(e.created) 
    ) as ex 
ON date(ex.created) =  date(t1.created) 
GROUP BY date(t1.created)) 
 
UNION 
 
(SELECT IFNULL(date(t1.created), date(ex.created)) as Date, t1.amount as Receive,ex.amount as ExpensesAmount  
FROM transactions as t1 
 
RIGHT JOIN ( 
    SELECT e.amount as amount, created 
    FROM expenses as e  
    group by date(e.created) 
) as ex 
ON date(t1.created) = date(ex.created) 
GROUP BY date(t1.created)) ORDER BY Date 

输出:

2019-10-01  300     NULL 
2019-11-01  200     NULL 
2019-11-13  230     600 

此处缺少日期 2019-11-13 的一笔交易。

预期结果:

2019-10-01  300     NULL 
2019-11-01  200     NULL 
2019-11-13  230     600 
2019-11-13  130     NULL 

在这里，我怎样才能得到我想要的结果？

请您参考如下方法:

如果您运行的是 MySQL 8.0，您可以将 row_number() 分配给来自每个具有 created 分区的表的记录，然后创建一个 left加入:

select t.created, t.amount, e.amount 
from ( 
    select  
        t.*,  
        row_number() over(partition by created order by id)  rn  
    from transactions t 
) t 
left join ( 
    select 
        e.*,  
        row_number() over(partition by created order by id)  rn  
    from expenses e 
) e 
    on e.created = t.created and e.rn = t.rn 

如果两个表中的任何一个可能有另一个表中不存在的日期，那么它就有点复杂了。基本上我们需要模拟 full join，这在 MySQL 中是不存在的。下面是使用 union all 的一种方法:

select created, max(t_amount) t_amount, max(e_amount) e_amount 
from ( 
    select  
        created, 
        amount t_amount, 
        null e_amount, 
        row_number() over(partition by created order by id)  rn  
    from transactions 
    union all 
    select  
        created, 
        null, 
        amount, 
        row_number() over(partition by created order by id) 
    from expenses 
) d 
group by created, rn