sql-server - 使用从特定数字开始的连续编号更新 T-SQL,并首先按其他列排序
问题描述
我在 SQL Server 2014 上使用这个。
用特定值(其中 ID = 0)从特定值(同一列中的最高值 + 1)开始并首先按另一列(时间戳)排序,更新列的最快方法是什么?
这是数据:
ID timestamp
-------------------------------
1 101 2020-01-01 11:02:14.235
2 102 2020-01-01 12:05:04.123
3 103 2020-01-01 13:20:50.457
4 104 2020-01-01 14:20:53.447
5 105 2020-01-01 15:25:25.125
6 0 2020-01-01 16:25:14.447
7 0 2020-01-02 20:26:01.147
8 0 2020-01-01 17:18:39.987
9 0 2020-01-01 19:14:14.014
10 0 2020-01-01 18:10:10.000
结果应该是这样的:
ID timestamp
-------------------------------
1 101 2020-01-01 11:02:14.235
2 102 2020-01-01 12:05:04.123
3 103 2020-01-01 13:20:50.457
4 104 2020-01-01 14:20:53.447
5 105 2020-01-01 15:25:25.125
6 106 2020-01-01 16:25:14.447
8 107 2020-01-01 17:18:39.987
10 108 2020-01-01 18:10:10.000
9 109 2020-01-01 19:14:14.014
7 110 2020-01-02 20:26:01.147
所以首先按时间戳排序,然后填充数字。为了速度,最好不要使用游标或临时表(它有很多表和很多数据库)。
解决方案
一个可更新的 CTE,使用ROW_NUMBER
和窗口化MAX
来获取新的 ID 值,似乎是这里最简单的解决方案:
CREATE TABLE dbo.YourTable (ID int,
[timestamp] datetime2(3)); --This is a bad name for a column, as timestamp is a synonym for rowversion
INSERT INTO dbo.YourTable (ID, [timestamp])
VALUES (101,'2020-01-01T11:02:14.235'),
(102,'2020-01-01T12:05:04.123'),
(103,'2020-01-01T13:20:50.457'),
(104,'2020-01-01T14:20:53.447'),
(105,'2020-01-01T15:25:25.125'),
( 0,'2020-01-01T16:25:14.447'),
( 0,'2020-01-02T20:26:01.147'),
( 0,'2020-01-01T17:18:39.987'),
( 0,'2020-01-01T19:14:14.014'),
( 0,'2020-01-01T18:10:10.000');
GO
WITH CTE AS(
SELECT ID,
ROW_NUMBER() OVER (PARTITION BY CASE ID WHEN 0 THEN 0 END ORDER BY [timestamp]) + MAX(ID) OVER () AS NewID
FROM dbo.YourTable)
UPDATE CTE
SET ID = NewID
WHERE ID = 0;
GO
SELECT *
FROM dbo.YourTable;
GO
注意:由于数据最左侧的数字似乎不是一列,因此数据的“顺序”将丢失;但是它并没有首先被保留,因为保证 RDBMS 中数据顺序的唯一ORDER BY
方法是为每一行创建一个唯一的“集合”。