时间戳

首页 > 解决方案 > 如何在一组行中创建行 ID

sql-server - 如何在一组行中创建行 ID

问题描述

我有一些数据，比如

Id, GroupId, Whatever
 1,        1,      10
 2,        1,      10
 3,        1,      10
 4,        2,      10
 5,        2,      10
 6,        3,      10

我需要添加一个“组行ID”列，例如

Id, GroupId, Whatever, GroupRowId
 1,        1,      10           1
 2,        1,      10           2
 3,        1,      10           3
 4,        2,      10           1
 5,        2,      10           2
 6,        3,      10           1

理想情况下，它将由数据库计算和执行。所以当我这样做的时候

INSERT INTO Foos (GroupId, Whatever) VALUES (1, 20)

我会得到正确的 GroupRowId。继续上面的示例数据，该行看起来像

Id, GroupId, Whatever, GroupRowId
 7,        1,      20           4

此数据将与第 3 方共享，其中一项要求是固定这些 GroupRowId，而不管任何不同的 ORDER BY 或 WHERE 子句如何。

我已经考虑了一个视图，row_id over/partition by但该视图在未来仍然可以修改，以破坏以前共享的数据。

我们的业务规则规定不会删除任何行，因此在这方面永远不需要重新计算 GroupRowId，并且永远不会有**缺失值。

** 在完美的商业规则世界中。

我的想法是，最好这是一个物理列，以便它存在于行中。它可以被查询并且不会基于 ORDER BY 或 WHERE 子句而改变。

标签： sql-servertsqlazure-sql-database

解决方案

你可以试试这个：

--创建一个测试数据库（最后会被丢弃！小心真实数据！！）

USE master;
GO
CREATE DATABASE GroupingTest;
GO
USE GroupingTest;
GO

--您的表，我为您的 Id 列使用 IDENTITY 列

CREATE TABLE dbo.tbl(Id INT IDENTITY,GroupId INT,Whatever INT);
GO

--插入你的测试值

INSERT INTO tbl(GroupId, Whatever)
VALUES
 (1,10)
,(1,10)
,(1,10)
,(2,10)
,(2,10)
,(3,10);
GO

--这是添加新列并最初填充它所必需的

ALTER TABLE tbl ADD GroupRowId INT;
GO
WITH cte AS
(
    SELECT GroupRowId    
          ,ROW_NUMBER() OVER(PARTITION BY GroupId ORDER BY Id) AS NewValue
    FROM tbl
)
UPDATE cte SET GroupRowId=NewValue;

--check the result
SELECT * FROM tbl ORDER BY GroupId,Id;
GO

--现在我们创建一个触发器，它对新行的
作用完全相同 --非常重要：这必须适用于单个插入和多个插入！

CREATE TRIGGER dbo.SetNextGroupRowId ON dbo.tbl
FOR INSERT
AS
BEGIN
    WITH cte AS
    (
        SELECT GroupRowId    
                ,ROW_NUMBER() OVER(PARTITION BY GroupId ORDER BY Id) AS NewValue
        FROM tbl
    )
    UPDATE cte 
    SET GroupRowId=NewValue
    WHERE GroupRowId IS NULL; --<-- this ensures to change only new rows
END
GO

--现在我们可以用一个值来测试它

INSERT INTO tbl(GroupId, Whatever)
VALUES(1,20);

SELECT * FROM tbl ORDER BY GroupId,Id;

--我们可以用多个插入来测试它

INSERT INTO tbl(GroupId, Whatever)
VALUES
 (1,30)
,(2,30)
,(2,30)
,(3,30)
,(4,30); --<-- the "4" is a new group

SELECT * FROM tbl ORDER BY GroupId,Id;
GO

- 打扫

USE master;
GO
DROP DATABASE GroupingTest;

你应该记住：

  • 这可能会给手动插入的值GroupRowId或任何其他语句对该列的任何操作带来麻烦。
  • 这可能会在删除行时遇到麻烦
  • 您可以考虑MAX(GroupRowId)+1为给定组选择一种方法。这取决于您的需求。
  • 您可以添加一个unique indexon GroupId,GroupRowId。这将 - 至少 - 避免两次给出相同的数字，但会导致错误。

...但是在您完美的业务规则世界中:-)这不会发生...

老实说：整个问题都有点味道……

推荐阅读