按日期重新排序/更新 SQL 行

Question

是否可以对sql表行重新排序？

我有大约 8000 行，它们有点混乱，因为日期与 id 相比是乱序的，我知道您可以通过删除并重新添加 id (INT auto imp) 来重置顺序，但我想按日期重新排序。

我知道我可以简单地从脚本中“按日期排序”，但我宁愿“按 ID 排序”并拥有一个干净的数据库...

我猜它是一个更新...查询，并已在高处和低处搜索了一个答案但尚未找到。

Answer 1

id 是用来做什么的？更改 ID 似乎没有问题，但如果它在其他地方引用，那就会出现问题。如果它没有在其他地方引用，那么您可能需要重新考虑您的数据库设计，因为该列可能会消失。

如果您仍然想这样做，您可以创建一个带有标识列的新表并插入不带 id 列的行数据，删除旧表并使用旧表的名称重命名新表。

Answer 2

我不同意你的目标，但我认为你可以编写一个过程，这样：

• 选择按日期排序的所有行，将它们保存在记录集中（使用游标）
• 从表中删除所有行
• 循环添加每一行，利用自动增量 id

Answer 3

_{我不太同意这个目标，但是。}

在 SQL Server 2005+ 中，您可以使用它。一次性完成此操作可以保持表约束、默认值等完好无损，同时仍然遵守其他表的所有外键挂钩。

create table badlyordered( id int identity, date datetime, otherdata varchar(100))
insert badlyordered(date) select getdate()+4
insert badlyordered(date) select getdate()+2
insert badlyordered(date) select getdate()+3
insert badlyordered(date) select getdate()
insert badlyordered(date) select getdate()+10
insert badlyordered(date) select getdate()-10
update badlyordered set otherdata = date

select * from badlyordered order by id

id          date                    otherdata
----------- ----------------------- ---------------------
7           2011-02-21 18:51:55.087 Feb 21 2011  6:51PM
8           2011-02-19 18:51:55.087 Feb 19 2011  6:51PM
9           2011-02-20 18:51:55.087 Feb 20 2011  6:51PM
10          2011-02-17 18:51:55.087 Feb 17 2011  6:51PM
11          2011-02-27 18:51:55.087 Feb 27 2011  6:51PM
12          2011-02-07 18:51:55.087 Feb  7 2011  6:51PM

;with tmp as (
select *,
table_seq = row_number() over (order by id),
nice_seq = row_number() over (order by date, id)
from badlyordered
)
update t1
set date = t2.date
   ,otherdata = t2.otherdata
   -- and any other columns in the table
from tmp t1
inner join tmp t2 on t1.table_seq=t2.nice_seq

select * from badlyordered order by id

id          date                    otherdata
----------- ----------------------- ---------------------
7           2011-02-07 18:51:55.087 Feb  7 2011  6:51PM
8           2011-02-17 18:51:55.087 Feb 17 2011  6:51PM
9           2011-02-19 18:51:55.087 Feb 19 2011  6:51PM
10          2011-02-20 18:51:55.087 Feb 20 2011  6:51PM
11          2011-02-21 18:51:55.087 Feb 21 2011  6:51PM
12          2011-02-27 18:51:55.087 Feb 27 2011  6:51PM

但我知道你正在使用MySQL。您可以通过使用变量在 MySQL 中对行编号两次，然后使用行号执行更新来执行相同的操作。

这会生成与 SQL Server 中的 CTE 相同的数据。

select ..(rownumber variable by date).. from
(
select ..(rownumber variable by id).. from tbl order by id
) n

然后，您需要其中 2 个（！）

select n.id, o.id
from
(

select ..(rownumber variable by date).. from
(
select ..(rownumber variable by id).. from tbl order by id
) n

inner join

select ..(rownumber variable by date).. from
(
select ..(rownumber variable by id).. from tbl order by id
) n

on ....
) o

来生成从原始到目标的更新 id 的映射。最后，您需要使用 UPDATE FROM JOIN MySQL 技术将表连接到这组 ID 来执行更新。