MySQL 选择查询以显示增量日期时间和默认值到某些列

Question

我有一个名为 bug_history_table 的表，如下所示

------------------------------------------------------------------------
| bug_id | date_modified       | field_changed | old_value | new_value |
------------------------------------------------------------------------
|  14415 | 2015-11-03 13:52:32 | status        | unconfirm | open      |
|  14415 | 2015-11-04 10:50:58 | status        | open      | resolved  |
|  14415 | 2015-11-10 11:27:41 | status        | resolved  | verified  |
|  14415 | 2015-11-14 11:27:41 | status        | verified  | closed    |
------------------------------------------------------------------------

上表显示了状态从打开到关闭的错误历史记录。我想显示错误闲置的两个日期之间的结果，即状态保持不变，直到它被修改。

预期输出-

--------------------------------------
| bug_id | date_modified | new_value |
--------------------------------------
|  14415 | 2015-11-03    | open      |
|  14415 | 2015-11-04    | resolved  |
|  14415 | 2015-11-05    | resolved  | <---
|  14415 | 2015-11-06    | resolved  | <---
|  14415 | 2015-11-07    | resolved  | <---  Need these extra rows.
|  14415 | 2015-11-08    | resolved  | <---
|  14415 | 2015-11-09    | resolved  | <---
|  14415 | 2015-11-10    | verified  |
|  14415 | 2015-11-11    | verified  | <---
|  14415 | 2015-11-12    | verified  | <---  Need these extra rows.
|  14415 | 2015-11-13    | verified  | <---
|  14415 | 2015-11-14    | closed    |
--------------------------------------

仅当错误状态被修改时才会插入条目。现在让我们举个例子如下 -

如果 bug 在 2015 年 11 月 3 日“打开”，然后直到 2015 年 11 月 6 日没有用户更新其状态，这意味着 bug 闲置了 3 天。所以我想显示这些天在 2015-11-03、2015-11-04、2015-11-05 处于“已打开”状态的错误。

Answer 1

制作此解决方案的最简单方法是使用过程，制作您需要的日期表并与该表进行左连接。

drop temporary table if exists tmp_dates;
create temporary table tmp_dates(_date timestamp);
set @tmp_date = start_date;
while @tmp_date <= end_date do

    insert into tmp_dates values (@tmp_date);
    set @tmp_date = @tmp_date+interval 1 day;

end while;

select a._date,b.bug_id,ifnull(b.new_value,'resolved',b.new_value) from tmp_dates a
left join  bug_history_table b on a._date = b.date_modified;