渐进式递增日期时间字段Mysql创建视图

Question

我想在mysql的两个表之间创建一个视图。

tbl1

| id_tbl1 | start_date          | delay | 
+---------+---------------------+-------+
|    1    | 2019-10-11 10:00:00 |  30   |
|    2    | 2019-10-12 18:00:00 |  0    |

tbl2

| id_tbl2 | id_tbl1 | user   | 
+---------+------------------+
|    1    |    1    |  Andy  |
|    2    |    1    |  Luke  |
|    3    |    2    |  Joe   |
|    4    |    1    |  Susy  |
|    5    |    2    |  Rick  |

我希望通过添加delay字段的值来逐步递增datetime字段。

结果应该是这样的

view1

| id_tbl1 | user  | start_date       |
+---------+-------+------------------+
|    1    | Andy  | 2019-10-11 10:00:00 |
|    1    | Luke  | 2019-10-11 10:00:30 |
|    1    | Susy  | 2019-10-11 10:01:00 |
|    2    | Joe   | 2019-10-12 18:00:00 |
|    2    | Rick  | 2019-10-12 18:00:00 |

编辑-我想增加秒数

有可能做到这一点吗？

我怎么能这么做呢？

谢谢

Answer 1

您可以使用join和row_number()对其进行寻址

select 
    t1.id_tbl1,
    t2.usr,
    date_add(
        t1.start_date, 
        interval (t1.delay * (row_number() over(partition by t1.id_tbl1 order by t2.id_tbl2 ) - 1)) second
    ) start_date
from tbl1 t1
inner join tbl2 t2 on t1.id_tbl1 = t2.id_tbl1 

注意:对于列名来说，user不是一个好的选择，因为它可能与保留字冲突。我将其更改为usr。

with 
    tbl1 as (
        select 1 id_tbl1, '2019-10-11 10:00' start_date, 30 delay from dual
        union all select 2, '2019-10-12 18:00', 0 from dual
    ),
    tbl2 as (
        select 1 id_tbl2, 1 id_tbl1, 'Andy' usr from dual
        union all select 2, 1, 'Luke' from dual
        union all select 3, 2, 'Joe' from dual
        union all select 4, 1, 'Susy' from dual
        union all select 5, 2, 'Rick' from dual
    )
select 
    t1.id_tbl1,
    t2.usr,
    date_add(
        t1.start_date, 
        interval (t1.delay * (row_number() over(partition by t1.id_tbl1 order by t2.id_tbl2 ) - 1)) second
    ) start_date
from tbl1 t1
inner join tbl2 t2 on t1.id_tbl1 = t2.id_tbl1;

| id_tbl1 | usr  | start_date          |
| ------- | ---- | ------------------- |
| 1       | Andy | 2019-10-11 10:00:00 |
| 1       | Luke | 2019-10-11 10:00:30 |
| 1       | Susy | 2019-10-11 10:01:00 |
| 2       | Joe  | 2019-10-12 18:00:00 |
| 2       | Rick | 2019-10-12 18:00:00 |

Answer 2

这是您的查询，您可以将您的delay列添加为start_date字段的interval。

select t1.id_tbl1 , t1.user
    , case when t1.id_tbl2 = 1 then t1.start_date
        else t1.start_date + interval t2.delay * (id_tbl2 - 1) second end
from tbl2 t1
left join tbl1 t2 on t1.id_tbl1 = t2.id_tbl1 

或者，由于您正在创建一个视图，您可能想要联接一个变量。

select t1.id_tbl1 , t1.user
    , t1.start_date 
            + interval t2.delay * (@curRow:=@curRow + 1) second
            - interval t2.delay second
from tbl2 t1
left join tbl1 t2 on t1.id_tbl1 = t2.id_tbl1 
join (select @curRow := 0) r;