mysql - SQL在按日期分组的已知集中缺失的表中插入值

Question

请提供有关如何最好地解决此问题的任何帮助。

为了简化，我有一个包含 5 个状态“A”到“E”的表格

我还有另一个表格，其中包含每天的状态量。每天都会插入新记录。当一个状态没有被量化时，它就没有被插入。我需要插入状态和日期并将 QTy 设置为 0。

create table dailyRecords(
  id int,
  status varchar(50),
  Qty int,
  myDate date
  )


  insert into dailyRecords values (1,'a',10,'2016-02-02');
  insert into dailyRecords values (2,'b',10,'2016-02-02');
  insert into dailyRecords values (3,'c',10,'2016-02-02');
  insert into dailyRecords values (4,'a',10,'2016-02-01');
  insert into dailyRecords values (5,'b',10,'2016-02-01');
  insert into dailyRecords values (6,'c',10,'2016-02-01');
  insert into dailyRecords values (7,'d',10,'2016-02-01');
  insert into dailyRecords values (8,'a',10,'2016-01-31');


create table status(
  id int,
  status varchar(50),
  )  

insert into status values (1,'a');
insert into status values (2,'b');
insert into status values (3,'c');
insert into status values (4,'d');
insert into status values (5,'e');
id  status Qty  date
 1,  A      10  2016-02-02
 2,  B      10  2016-02-02
 3,  C      10  2016-02-02
 4,  A      10  2016-02-01
 5,  B      10  2016-02-01
 6,  D      10  2016-02-01
 7,  E      10  2016-02-01

将每天缺失的状态插入为:

 8,  D      0  2016-02-02
 9,  E      0  2016-02-02

10,  C      0  2016-02-01

这是一个示例代码: http://sqlfiddle.com/#!3/8b1029/6

我搜索了论坛，但添加日期的问题让我很困惑。

有什么帮助吗？

谢谢!

Answer 1

您可以使用 cross join 生成包含所有日期和状态的行。然后你可以left join获取已有的值:

select d.date, s.status, coalesce(dr.qty, 0) as qty
from (select distinct date from dailyRecords) d cross join
     (select status from dailyRecords) s left join
     dailyRecords dr
     on dr.date = d.date and dr.status = s.status;

如果将这些插入回表中，则可以将第一列设置为自动递增的 id。