如何在一组行中创建行id

Question

我有一些类似的数据

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

该数据将与第三方共享，其中一项要求是无论ORDER BY或WHERE子句有何不同，都要固定这些GroupRowId。

我已经考虑过一个具有row_id over/partition by的视图，但该视图在未来仍可能被修改，从而破坏以前共享的数据。

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

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

我的想法是，最好这是一个物理列，这样它就存在于行中。它可以查询，并且不会根据ORDERBY或WHERE子句进行更改。

Answer 1

您可以尝试以下方法：

--创建一个测试数据库(将在最后删除！小心真实数据！！(

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

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

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的方法。这取决于你的需要
• 您可以在GroupId,GroupRowId上添加一个unique index。这至少可以避免两次给出相同的数字，但会导致错误

。。。但在您的完美商业规则世界中：-(这不会发生。。。

老实说：整个问题有一些味道。。。