sqlserver update语句逐行生效

Question

sqlserver update 语句
每一行两个字段，名称，组号
如果这一行名称与上一行名称一致，则组号相同。
如果名称不一致，则组号为空。

由于update是一个事务，不会产生连锁反应，只会生效一行。

除了写游标，有没有一次性到位的update写法

Answer 1

该回答结合ChatGPT4o及杨同学*共同作答, 如有帮助，还请采纳。
要在 SQL Server 中实现按名称更新组号，可以使用窗口函数和 UPDATE 语句。虽然在单个事务中更新的行不会相互影响，但可以借助 ROW_NUMBER 和 PARTITION BY 等窗口函数来计算组号。

以下是一个示例脚本，假设您的表名为 #productionSeq，需要将 seq 字段根据 productcode 字段的变化进行更新：

-- 创建示例表和数据
CREATE TABLE #productionSeq (
    id INT,
    productcode VARCHAR(20),
    startTime DATETIME,
    endTime DATETIME,
    seq INT
);

INSERT INTO #productionSeq (id, productcode, startTime, endTime)
VALUES
(1, 'Y0536180-01', '2024-07-25 07:35:04.457', '2024-07-25 08:10:34.457'),
(2, 'Y0536180-01', '2024-07-25 07:51:03.743', '2024-07-25 08:11:07.900'),
(3, 'Y0536180-01', '2024-07-25 07:47:16.083', '2024-07-25 08:11:22.497'),
(4, 'Y0536180-01', '2024-07-25 07:51:53.083', '2024-07-25 08:11:46.090'),
(5, 'Y0536180-01', '2024-07-25 07:49:45.807', '2024-07-25 08:12:07.193'),
(6, 'Y0536180-01', '2024-07-25 07:48:38.083', '2024-07-25 08:12:29.640'),
(7, 'Y0536180-01', '2024-07-25 07:45:31.463', '2024-07-25 08:12:39.263'),
(8, 'Y0536180-01', '2024-07-25 07:51:34.083', '2024-07-25 08:15:17.000'),
(9, 'Y0536180-01', '2024-07-25 07:46:42.783', '2024-07-25 08:16:34.500'),
(10, 'Y0536180-01', '2024-07-25 07:45:26.083', '2024-07-25 08:16:44.960'),
(11, 'Y0536180-01', '2024-07-25 07:49:23.207', '2024-07-25 08:19:16.193'),
(12, 'Y0536180-01', '2024-07-25 07:48:34.633', '2024-07-25 08:19:63.063'),
(13, 'Y0536180-01', '2024-07-25 07:49:48.110', '2024-07-25 08:20:19.240'),
(14, 'Y0536180-01', '2024-07-25 08:11:04.860', '2024-07-25 08:20:45.193'),
(15, 'Y0536180-01', '2024-07-25 08:15:48.020', '2024-07-25 08:21:22.240');

-- 使用窗口函数计算组号
WITH NumberedSeq AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM #productionSeq
),
GroupedSeq AS (
    SELECT *,
           CASE WHEN LAG(productcode) OVER (ORDER BY rn) = productcode THEN 1 ELSE NULL END AS new_seq
    FROM NumberedSeq
)
UPDATE #productionSeq
SET seq = new_seq
FROM GroupedSeq
WHERE #productionSeq.id = GroupedSeq.id;

-- 查看结果
SELECT * FROM #productionSeq;

-- 清理示例表
DROP TABLE #productionSeq;

在这个示例中：

1. 创建了一个临时表 #productionSeq 并插入示例数据。
2. 使用窗口函数 ROW_NUMBER() 给每一行赋予一个行号。
3. 使用 LAG() 函数比较当前行的 productcode 与前一行的 productcode，并根据结果生成新的 seq 值。
4. 使用 UPDATE 语句将计算出来的 seq 值更新回原表。

这样可以一次性更新所有行的 seq 值，而不需要使用游标。