mysql - SQL 将库存盘点表连接到日期表

Question

我有一个不同产品的运行库存表，记录每次交易后的库存计数。交易并非每天都发生，因此该表没有每天运行的计数。

我需要列出每个产品的所有日期，以便我可以对一段时间内的计数求和并取平均值。

库存

DATE        ID  Qty Count
2014-05-13  123 12  12
2014-05-19  123 -1  11
2014-05-28  123 -1  10
2014-05-29  123 -3  7
2014-05-10  124 5   5
2014-05-15  124 -1  4
2014-05-21  124 -1  3
2014-05-23  124 -3  0

我有一个包含连接日期的表格，但我不确定如何让缺失的日期连接多个产品。

我需要如下查询。它需要返回所选时间段内的计数，但也包括其间的日期。

DATE        ID  Qty Count
2013-05-01  123 0   0
2013-05-02  123 0   0
2013-05-03  123 0   0
2013-05-04  123 0   0
2013-05-05  123 0   0
2013-05-06  123 0   0
2013-05-07  123 0   0
2013-05-08  123 0   0
2013-05-09  123 0   0
2013-05-10  123 0   0
2013-05-11  123 0   0
2013-05-12  123 0   0
2014-05-13  123 12  12
2013-05-14  123 0   12
2013-05-15  123 0   12
2013-05-16  123 0   12
2013-05-17  123 0   12
2013-05-18  123 0   12
2014-05-19  123 -1  11
2013-05-20  123 0   11
2013-05-21  123 0   11
2013-05-22  123 0   11
2013-05-23  123 0   11
2013-05-24  123 0   11
2013-05-25  123 0   11
2013-05-26  123 0   11
2013-05-27  123 0   11
2014-05-28  123 -1  10
2014-05-29  123 -3  7
2013-05-30  123 0   7
2013-05-31  123 0   7
2013-05-01  124 0   0
2013-05-02  124 0   0
2013-05-03  124 0   0
2013-05-04  124 0   0
2013-05-05  124 0   0
2013-05-06  124 0   0
2013-05-07  124 0   0
2013-05-08  124 0   0
2013-05-09  124 0   0
2014-05-10  124 5   5
2014-05-11  124 0   5
2014-05-12  124 0   5
2014-05-13  124 0   5
2014-05-14  124 0   5
2014-05-15  124 -1  4
2014-05-16  124 0   4
2014-05-17  124 0   4
2014-05-18  124 0   4
2014-05-19  124 0   4
2014-05-20  124 0   4
2014-05-21  124 -1  3
2014-05-22  124 0   3
2014-05-23  124 -3  0
2014-05-24  124 0   0
2014-05-25  124 0   0
2014-05-26  124 0   0
2014-05-27  124 0   0
2014-05-28  124 0   0
2014-05-29  124 0   0
2014-05-30  124 0   0
2014-05-31  124 0   0

Answer 1

使用inv join inv 建立至少31 行并构造一个31 天的表。然后加入ids，最后加入原始表。

select a.d, a.id, a.qty,
  if(a.id=@lastid, @count:=@count+a.qty, @count:=a.count) `count`,
  @lastid:=a.id _lastid
from (
  select a.d, b.id, ifnull(c.qty, 0) qty, ifnull(c.count, 0) `count`
  from (
    select adddate('2014-05-01', @row) d, @row:=@row+1 i
    from inv a
    join inv b
    join (select @row := 0) c
    limit 31) a
  join (
    select distinct id
    from inv) b
  left join inv c on a.d = c.date and b.id = c.id
  order by b.id, a.d) a
join (select @count := 0, @lastid := 0) b;

fiddle