SQL Server语言 - 用于更新表中的日期时间的 SQL 作业

Question

我有一个表格，其中包含我要维护的演示数据。 表中的数据包含日期时间，我想创建一个每晚运行的作业来更新日期时间，以便日期时间永远不会超过 1 周，并且永远不会有未来的日期时间，然后是当前日期时间。 我想在按日期时间排序时保持消息的顺序，以便在更新该列时，一行永远不会跳过另一行。 我无法想出一个公式来做到这一点，除非按日期时间排序时某些行最终会跳过。有什么建议吗？

我的问题在于公式。如果日期为周二下午 5 点，则向所有人添加一天仍将使其成为最后一条消息的未来日期，我添加 1 天，然后在周三早上演示时是周三下午 5 点。添加一天（最近一天除外）可能会跳过下一个最接近的日期，从而导致最新的消息按顺序回退。

Answer 1

如果您当前有多个日期跨越一周以上，则无法通过简单地为每个日期添加时间来实现。如果记录之间的时间范围无关紧要，只有顺序、最小值和最大值，我会使用 row_number 函数对日期进行排序，并在几秒钟内将该排序数字添加到 1 周前：

update t
    set t.YourDateColumn = u.NewDate
    from YourTable t
        inner join  (select ID,
                        dateadd(second,
                            row_number() over(order by YourDateColumn),
                            dateadd(week,-1,getdate())) NewDate
                     from YourTable
                     ) u
            on u.ID = t.ID

如果您有多达 604.8K 条记录，这将起作用。如果你有更多的时间，你将不得不增加毫秒数。这将适用于多达 604.8M 条记录。

Answer 2

假设你使用的是MySQL，因为你没有指定（尽管同样的技术应该在任何地方都可以，但使用的确切语法和函数会有所不同）

给定这样的表结构

CREATE TABLE T (
  id int auto_increment primary key, 
  detail varchar(20), 
  date datetime
);
INSERT INTO T
  (detail, date)
VALUES
  ('one', NOW()),
  ('two', DATE_SUB(NOW(), INTERVAL 1 day)),
  ('three', DATE_SUB(NOW(), INTERVAL 2 day)),
  ('four', DATE_SUB(NOW(), INTERVAL 3 day)),
  ('five', DATE_SUB(NOW(), INTERVAL 4 day));

您可以按正确的顺序选择所有行，并在此过程中将日期均匀地重新分布在前一周。喜欢这个：

SELECT 
  T.*, 
  DATE_SUB(NOW(), INTERVAL 6.048e+11*(1-(@row := @row + 1)/@tot) MICROSECOND) as newDate 
FROM 
  T, 
  (SELECT @row := 0, @tot := (select count(*) from T)) r 
ORDER BY 
  date;

注意：6.048e+11是一周中的微秒数。

下面是一个示例：http://sqlfiddle.com/#!2/939bc/5

因此，您可以使用该投影创建一个临时表，然后删除原始表，然后从临时表中的值重新创建它。 像这样：

create temporary table T2 as (
  select 
    T.*, 
    DATE_SUB(NOW(), INTERVAL 6.048e+11*(1-(@row := @row + 1)/@tot) MICROSECOND) as newDate
  from T, (SELECT @row := 0, @tot := (select count(*) from T)) r 
  order by date
);
drop table T;
create table T as (select id, detail, newDate as date from T2);

更新：将上述示例更改为使用微秒粒度，因为前面的示例使用了分数DAY间隔，mysql 似乎不支持。 我还相应地更新了 sqlfiddle 链接。

此外，还更正了公式以正确的顺序对日期进行排序（前面的示例按所需的相反顺序分配日期）。