我有一个ID为主键(也有身份)和datetimestamp列的表。我需要使用按时间戳值排序的ID更新表,如下所示。并非所有ID都存在。这些ID是正确的,并且datetimestamps杂乱无章,需要进行排序。
数据库表,当前数据为-
id datetimestamp
-- -----------------------
1 2013-08-08 14:08:43.560
2 2013-08-05 14:08:46.963
4 2013-08-06 14:08:53.247
5 2013-08-04 14:08:55.610
6 2013-08-03 14:08:58.543
8 2013-08-05 14:08:46.963
9 2013-08-06 14:08:53.247
10 2013-08-04 14:08:55.610
11 2013-08-03 14:08:58.543
所需数据为-
id datetimestamp
-- -----------------------
1 2013-08-03 14:08:58.543
2 2013-08-03 14:08:58.543
4 2013-08-04 14:08:55.610
5 2013-08-04 14:08:55.610
6 2013-08-05 14:08:46.963
8 2013-08-05 14:08:46.963
9 2013-08-06 14:08:53.247
10 2013-08-06 14:08:53.247
11 2013-08-08 14:08:43.560
以下是可以创建示例数据的脚本-
create table #tmp_play
(id int identity (1,1) primary key, datetimestamp datetime)
insert into #tmp_play values (getdate());
insert into #tmp_play values (getdate()-3);
insert into #tmp_play values (getdate()-1);
insert into #tmp_play values (getdate()-2);
insert into #tmp_play values (getdate()-4);
insert into #tmp_play values (getdate()-5);
delete from #tmp_play where id = 3
insert into #tmp_play (datetimestamp)
select datetimestamp from #tmp_play
delete from #tmp_play where id = 7
我尝试了以下方法,但是由于缺少ID,因此无法使用。
with sorted as
(select top 100 ROW_NUMBER() OVER(ORDER BY datetimestamp) as RowNum, *
from #tmp_play order by datetimestamp)
update t
set t.datetimestamp = s.datetimestamp
from #tmp_play t
join sorted s on t.id = s.RowNum
知道如何对这些数据进行排序吗?
为什么顺序很重要?对应用程序或企业而言,id 1的时间值小于或大于id 2无关紧要。我知道您正在尝试修复被认为是坏数据的内容,但这确实不会影响您的应用程序。我也同意,此序列号可能没有更好的价值,因此可能会更好地推导。
话虽如此,要解决当前的问题,您需要获得两组序列号。1代表datetimestamp,另一个代表id。然后,您可以结合这两个来更新行。
;with Id_Order
AS
(
select *, id_seq = ROW_NUMBER() over(order by id)
from #tmp_play
),
Dt_Order
as
(
select *, dt_seq = ROW_NUMBER() over(order by datetimestamp asc)
from #tmp_play
)
update a
set datetimestamp = dt.datetimestamp
from Id_Order a
inner join Dt_Order dt
on a.id_seq = dt.dt_seq
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句