如何为每一行将 DateTime 列增加一秒? [英] How to increment DateTime column by a second for every row?
问题描述
假设我有这个时间值:09:00:00
我有一个包含时间列的表,其中有 3 条记录.
And I have a table with a column Time, and I have three records in it.
我想用那个时间更新这 3 条记录,但 Time 值每次增加一秒(对于每条记录).
I want to update those 3 records with that time but with the Time value incremented in one second each time (for every record).
像这样:
ColumnA ColumnB
1 09:00:00
2 09:00:01
3 09:00:02
我该怎么做?
我的解决方案:
在我自己的解决方案上工作了一段时间后,这就是我想出的
After some time working on my own solution, this is what I came up with
update tor1
set ColumnB = dateadd(s,tor2.inc, ColumnB)
from table1 tor1
inner join (select ColumnA, ROW_NUMBER() OVER (Order by ColumnA) as inc from table1) tor2 on tor1.ColumnA=tor2.ColumnA
推荐答案
您没有指定任何特定的顺序.
You don't specify any particular ordering.
对于不确定/未记录的结果,您可以尝试古怪的更新方法.
For undeterministic/undocumented results you could try the quirky update approach.
CREATE TABLE table1
(
ColumnB datetime NULL
);
INSERT INTO table1 DEFAULT VALUES;
INSERT INTO table1 DEFAULT VALUES;
INSERT INTO table1 DEFAULT VALUES;
DECLARE @ColumnB datetime;
SET @ColumnB = '19000101 09:00:00';
UPDATE table1
SET @ColumnB = ColumnB = DATEADD(s, 1, @ColumnB);
SELECT *
FROM table1;
DROP TABLE table1;
否则您将需要使用游标或找到某种方法来模拟 2000 年的 ROW_NUMBER
.
Otherwise you will need to use a cursor or find some way of simulating ROW_NUMBER
in 2000.
这篇关于如何为每一行将 DateTime 列增加一秒?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!