mysql - 通过 SELECT..INSERT 语句填充空记录

Question

首先，这是数据库的简化架构(带有虚拟记录):

项目列表

ItemID     ItemName     DateAcquired     Cost       MonthlyDep    CurrentValue
================================================================================
1          Stuff Toy    2011-12-25       100.00         10.00         100.00
2          Mouse        2011-12-23       250.00         50.00         200.00
3          Keyboard     2011-12-17       250.00         30.00         190.00
4          Umbrella     2011-12-28       150.00         20.00         110.00
5          Aircon       2011-12-29       950.00         25.00         925.00

折旧交易

ItemID     DateOfDep       MonthlyDep     
======================================
2          2012-01-31         250.00
3          2012-01-31          30.00
4          2012-01-31          20.00
5          2012-01-31          25.00
3          2012-02-29          30.00
4          2012-02-29          20.00

我需要您的建议来帮助我解决这个问题。基本上我正在创建某个地方政府单位的折旧监控系统。当前数据库的问题是缺少一些特定折旧日期的记录，例如:

缺少记录(这不是数据库中的表)

ItemID      LackingDate
============================
1            2012-01-31
1            2012-02-29
2            2012-02-29
5            2012-02-29

由于缺少记录，我无法生成 MARCH 月份的折旧报告。知道如何在 DepreciationTransaction 上插入缺失的记录吗？

到目前为止我做了什么？ 没有。但是计算新折旧值的简单查询(由于缺少记录而产生不正确的值)

Answer 1

这里的问题是您必须生成数据。 MySQL 无意生成数据，您应该在应用程序级别执行此操作，并告诉 MySQL 存储它。在这种情况下，应用程序应该检查是否有丢失的记录，并在需要时创建它们。

抛开这一点，您可以(非常)使用 MySQL 创建动态数据，如下所示:

select il.itemId, endOfMonths.aDate from ((
  select aDate from (
    select @maxDate - interval (a.a+(10*b.a)+(100*c.a)+(1000*d.a)) day aDate from
    (select 0 as a union all select 1 union all select 2 union all select 3
     union all select 4 union all select 5 union all select 6 union all
     select 7 union all select 8 union all select 9) a, /*10 day range*/
    (select 0 as a union all select 1 union all select 2 union all select 3
     union all select 4 union all select 5 union all select 6 union all
     select 7 union all select 8 union all select 9) b, /*100 day range*/
    (select 0 as a union all select 1 union all select 2 union all select 3
     union all select 4 union all select 5 union all select 6 union all
     select 7 union all select 8 union all select 9) c, /*1000 day range*/
    (select 0 as a union all select 1 union all select 2 union all select 3
     union all select 4 union all select 5 union all select 6 union all
     select 7 union all select 8 union all select 9) d, /*10000 day range*/
    (select @minDate := (select min(dateAcquired) from il),
            @maxDate := '2012-03-01') e
  ) f
  where aDate between @minDate and @maxDate and aDate = last_day(aDate)
) endOfMonths, il)
left join dt
on il.itemId = dt.itemId and endOfMonths.aDate = dt.dateOfDep
where dt.itemId is null and last_day(il.dateAcquired) < endOfMonths.aDate

根据日期范围的长度，您可以通过删除表(d、c、b 和 a)并将其从上式。设置 @minDate 和 @maxDate 变量将允许您指定要过滤结果的日期。根据您的情况，此日期应该是您拥有商品的最短日期，最长日期应该是三月。

用简单的英语来说:如果 select min(dateAcquired) from il 返回 '2012-03-01' - 10000 天 之前的日期，那么您必须添加另一个工会。

最后，只需添加插入语句(如果您确实需要插入这些记录)。