使用更新语句增加日期时间字段 [英] Incrementing datetime field with an update statement

问题描述

所以我有一个包含许多日期时间字段的表，当我下降列表时，我必须将每个日期增加 1 天.所以它们存在的日期将是:

So I've got a table that contains a number of datetime fields, and I have to increment each date by 1 day as I descend the list. So the dates as they exist would be:

2011-04-19
2011-04-19
2011-04-19
2011-04-19

我需要运行一个语句让它们看起来像:

And I need to run a statement to make them look like:

2011-04-19
2011-04-20
2011-04-21
2011-04-22

所以基本上，第一行保持不变，第二行增加了一天，第三行增加了 2 天，第四行增加了 3 天，以此类推.

So basically, the first row stays the same, the second row gets a day added, the third gets 2 days added, the fourth gets 3 days added, etc.

我一直在寻找，但不知道该怎么做.我不想使用游标，所以如果有人有任何建议，将不胜感激.谢谢！

I've been searching but can't quite figure out what to do. I'd prefer not to use a cursor, so if anyone has any advice it would be much appreciated. Thanks!

推荐答案

您可以使用会话变量:

SET @r := -1;

UPDATE  mytable
SET     mydate = mydate + INTERVAL (@r := @r + 1) DAY;

在 SQL Server 2005 中:

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (ORDER BY mydate) rn
        FROM    mytable
        )
UPDATE  q
SET     mydate = DATEADD(d, rn - 1, mydate)

这篇关于使用更新语句增加日期时间字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！