首页 > sql > 复制数据库的值并调整其日期时间

复制数据库的值并调整其日期时间 (Copy values of a database and adjust their datetime)

问题

我想创建新的表行。我从原始表中获取一个条目并将此项目复制到一个新行。但DateTime值总是增加一个月。我想循环我的条目项表中的每个项目。

我原来的表:

ID  MyDateTime
1   2018-07-22 11:38:23.123
2   2018-07-22 11:39:23.123

我的预期结果:

ID  MyDateTime
1   2018-07-22 11:38:23.123
2   2018-07-22 11:39:23.123
3   2018-08-22 11:38:23.123
4   2018-08-22 11:39:23.123

这是我现在所拥有的,但我坚持:https//pastebin.com/CjhAJ49Q

解决方法

如果要更改表,请使用insert

insert into t (MyDateTime)
    select dateadd(month, 1, MyDateTime)
    from t
    order by MyDateTime;

如果您只是想要一个产生结果的查询,那么:

select row_number() over (order by MyDateTime) as id,
       MyDateTime
from ((select MyDateTime from t) union all
      (select dateadd(month, 1, MyDateTime) from t
     ) t
order by MyDateTime;

问题

I want to create new table rows. I take an entry from my original table and copy this item into a new row. But the DateTime value is always increased by one month. I want to loop every item in my entry item table.

My original table:

ID  MyDateTime
1   2018-07-22 11:38:23.123
2   2018-07-22 11:39:23.123

My expected result:

ID  MyDateTime
1   2018-07-22 11:38:23.123
2   2018-07-22 11:39:23.123
3   2018-08-22 11:38:23.123
4   2018-08-22 11:39:23.123

Here is what I have until now but I stuck: https://pastebin.com/CjhAJ49Q

解决方法

If you want to change table, then use insert:

insert into t (MyDateTime)
    select dateadd(month, 1, MyDateTime)
    from t
    order by MyDateTime;

If you simply want a query that produces the results, then:

select row_number() over (order by MyDateTime) as id,
       MyDateTime
from ((select MyDateTime from t) union all
      (select dateadd(month, 1, MyDateTime) from t
     ) t
order by MyDateTime;
相似信息