mysql - 如何连接两个不相关的mysql表并使用按日期分组

Question

我有 2 张表凭证和分类帐，字段是分类账:

id  total_sale  cancel_amount  date
1    3000           0           2018-01-20
2    3000           0           2018-01-29
3    5000           0           2018-01-30
4    10000          500         2018-01-30
5    2000           100         2018-01-31
6    2000           0           2018-01-31

优惠券:

id  expenditure    date
1    500          2018-01-20
2    800          2018-01-30
3    1000         2018-01-30
4    200          2018-01-31
5    300          2018-01-31  

我想要一个类似于[日期在2018-01-29到2018-01-31之间]的结果

date           total_sale   total_expenditure
2018-01-29       3000           0
2018-01-30       15000         1800
2018-01-31       4000          500

请有人帮忙

Answer 1

对于此类要求，始终首选维度表。这样您就不会在连接两个没有共同键的不同表时感到困惑。

表格:

create schema test;

create table date_dim(date date);
insert into date_dim values ('2018-01-20'),
('2018-01-21'),
('2018-01-22'),
('2018-01-23'),
('2018-01-24'),
('2018-01-25'),
('2018-01-26'),
('2018-01-27'),
('2018-01-28'),
('2018-01-29'),
('2018-01-30'),
('2018-01-31');

create table ledger(id int,total_sale int, cancel_amount int, date date);

insert into ledger values
(1,3000,0,'2018-01-20'),
(2,3000,0,'2018-01-29'),
(3,5000,0,'2018-01-30'),
(4,10000,500,'2018-01-30'),
(5,2000,100,'2018-01-31'),
(6,2000,0,'2018-01-31');

create table voucher(id int, expenditure int, date date);
insert into voucher values
(1,500,'2018-01-20'),
(2,800,'2018-01-30'),
(3,1000,'2018-01-30'),
(4,200,'2018-01-31'),
(5,300,'2018-01-31');

SQL 脚本(用于解决方案):

 select l.date,total_sale,
total_expenditure
from 
(select d.date date,sum(total_sale)total_sale
 from ledger l right join date_dim d on l.date=d.date
 where d.date between '2018-01-29' and '2018-01-31'
group by 1)l

 join 
(select d.date date,sum(expenditure)total_expenditure
 from voucher v right join date_dim d on v.date=d.date
 where d.date between '2018-01-29' and '2018-01-31'
group by 1)v
on l.date=v.date
group by 1,2,3;

结果集:

date       total_sale   total_expenditure
2018-01-29   3000        (null)
2018-01-30   15000        1800
2018-01-31   4000         500

查看解决方案 SQL Fiddle