MySQL查询以更新日期的记录 [英] MySQL query to update records with incremented date

问题描述

我正在尝试

1. 获取数据库中的最新日期，并且
2. 根据该日期更新每个具有 NULL 日期的记录，将日期增加 1 天.

我可以使用下面的最新日期查询来获取最新日期.我需要先执行此操作，因为表中的日期按顺序不是.如果需要，我可以运行此查询，手动将其写下来，然后根据此日期运行 UPDATE 查询.我宁愿在没有手动过程的情况下运行所有​​内容.

我在问题底部的最后一个查询是我的测试查询，用于尝试更新日期，但是我没有运气让它工作.

表格(日期不按顺序)

id 日期----- ----------10500 2013-08-1810501 2013-08-1610502 2013-08-1710503 2013-08-1910504 空10505 空...11800 空11801 空

选择最新日期(UPDATE 的起点)

选择日期FROM my_table按日期排序限制 1

更新 NULL 日期(不起作用)

更新 my_tableSET date = DATE_ADD((SELECT date FROM my_table ORDER BY date DESC LIMIT 1), INTERVAL 1 DAY)日期为空ORDER BY id ASC

我怎样才能做到这一点?或者这不可能?

解决方案

试试

UPDATE Table1 t1 JOIN(选择 id, @n := @n + 1 rnum从表 1 交叉连接(选择 @n := 0)我日期为空按编号订购) t2 ON t1.id = t2.id 交叉连接(从表 1 中选择最大(日期)日期) qSET t1.date = q.date + INTERVAL t2.rnum DAY

结果:

<上一页>|身份证 |日期 |----------------------|10500 |2013-08-18 ||10501 |2013-08-16 ||10502 |2013-08-17 ||10503 |2013-08-19 ||10504 |2013-08-20 |-- 已指定日期|10505 |2013-08-21 |-- 已指定日期

这里是 SQLFiddle 演示

说明:在一个别名为 t2 的子查询中，我们抓取所有日期为 NULL 的行，按 id 对其进行排序，并从 1 开始分配行号.不幸的是，MySql 没有实现对于 ROW_NUMBER() 函数，因此我们使用用户变量 @n 来执行此操作，该变量在选择行时递增.为了初始化这个变量，我们使用一个别名为 i 的子查询.并使用 CROSS JOIN 使其可用于我们的子查询 t2.然后我们使用相同的技术(CROSS JOIN)来获取表中的最大日期，并使其可用于我们的JOIN 中的每一行.一旦我们拥有了所有内容，我们只需添加一个行号，它代表天数)将其添加到最大日期并分配给我们表中的 date 列.

I am trying to

1. get the latest date in a database, and
2. based on that date update every record that has a NULL date, increasing the date by 1 day.

I can get the latest date by using the Latest Date query below. I need to do this first because the dates in the table are not in order. If need be, I can run this query, manually write it down, then run the UPDATE query based on this date. I would prefer to run everything without the manual process.

The last query I have at the bottom of the question is my test query for trying to update the dates, however I had no luck getting it to work.

Table (dates are not in order)

id     date
-----  ----------
10500  2013-08-18
10501  2013-08-16
10502  2013-08-17
10503  2013-08-19
10504  NULL
10505  NULL
...
11800  NULL
11801  NULL

Selecting the latest date (starting point for UPDATE)

SELECT date
FROM my_table
ORDER BY date DESC
LIMIT 1

Updating NULL dates (doesn't work)

UPDATE my_table
SET date = DATE_ADD((SELECT date FROM my_table ORDER BY date DESC LIMIT 1), INTERVAL 1 DAY)
WHERE date IS NULL
ORDER BY id ASC

How can I accomplish this? Or is this not possible?

解决方案

Try

UPDATE Table1 t1 JOIN
(
  SELECT id, @n := @n + 1 rnum
    FROM Table1 CROSS JOIN (SELECT @n := 0) i
   WHERE date IS NULL
   ORDER BY id
) t2 ON t1.id = t2.id CROSS JOIN
(
  SELECT MAX(date) date FROM Table1
) q
   SET t1.date = q.date + INTERVAL t2.rnum DAY

Result:

|    ID |       DATE |
----------------------
| 10500 | 2013-08-18 |
| 10501 | 2013-08-16 |
| 10502 | 2013-08-17 |
| 10503 | 2013-08-19 |
| 10504 | 2013-08-20 | --  date has been assigned
| 10505 | 2013-08-21 | --  date has been assigned

Here is SQLFiddle demo

Explanation: In a subquery with an alias t2 we grab all rows where date IS NULL order them by id and assign row numbers starting from 1. Unfortunately MySql doesn't have an implementation for ROW_NUMBER() function so we do it with a user variable @n which is incremented while rows are selected. To initialize this variable we use a subquery with an alias i. And use CROSS JOIN to make it available for our subquery t2. We then use the same technique (CROSS JOIN) to grab a max date in the table and make it available for every row in our JOIN. ONce we have all that we just add a line number, which represents a number of days) add it to the max date and assign to date column in our table.

这篇关于MySQL查询以更新日期的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！