类似于身份的列，但基于分组依据条件 [英] Identity-like column but based on Group By criteria

问题描述

在SQL Server 2012数据库中，我正在创建一个任务"表，该表将具有由三个字段组成的复合主键:

In my SQL Server 2012 database, I'm creating a "Tasks" table that will have a compound primary key composed of three fields:

Issue_ID [int] NOT NULL,
Issue_Sub_ID [int] NOT NULL,
Task_ID [int] NOT NULL

Issue_ID和Issue_Sub_ID是其他表的外键.在我创建的表中，与每个Issue_ID/Issue_Sub_ID组合相关的任务很多.

Issue_ID and Issue_Sub_ID are foreign keys to other tables. In the table I'm creating, there can be many tasks associated with each Issue_ID / Issue_Sub_ID combination.

我想做的是为Task_ID列建立一个默认值，类似于我使用IDENTITY(1,1)的情况，但是它将基于Issue_ID/Issue_Sub_ID组自动增加.例如，给定提供的Issue_ID/Issue_Sub_ID值，Task_ID结果如下所示:

What I would like to do is establish a default value for the Task_ID column, similar to if I used IDENTITY(1,1), but that will auto-increment based on the Issue_ID / Issue_Sub_ID group. For example, the Task_ID results would look as follows, given the provided Issue_ID / Issue_Sub_ID values:

Issue_ID     Issue_Sub_ID     Task_ID
========     ============     =======
12345             1              1
12345             1              2
12345             1              3
12345             2              1
12345             2              2
67890             2              1
67890             2              2
67890             2              3

我熟悉ROW_NUMBER() OVER(PARTITION BY Issue_ID, Issue_Sub_ID ORDER BY Issue_ID, Issue_Sub_ID)可能的解决方案，但是，由于我希望此列成为表的复合主键的一部分，所以我认为这行不通.

I'm familiar with the ROW_NUMBER() OVER(PARTITION BY Issue_ID, Issue_Sub_ID ORDER BY Issue_ID, Issue_Sub_ID) possible solution but, as I'd like this column to be a part of the compound primary key of the table, I don't think that will work.

先谢谢大家.

推荐答案

我同意Sean的观点-添加一个Identity列，然后仅将计算所得的列用作任务ID. 即使我已经在此处回答了一个非常类似的问题， 我不确定将其标记为重复项.这样做的原因是您要将task_id用作主键的一部分.
但是，我不确定这是否可行，因为为了在主键中包含计算列，它必须为persisted，并且由于某些原因(我认为是由于使用了UDF)，SQL Server不允许我将其标记为持久.
无论如何，这是我为此建议的解决方案:

I agree with Sean - add an identity column, and then just use a computed column for the task id. Even though I've answered a question very much like this one here, I'm not sure about marking this one as a duplicate. The reason for this is that you want to use the task_id as a part of the primary key.
However, I'm not sure that's possible, since in order to include a computed column in the primary key it must be persisted, and for some reason (I think it's because of the use of a UDF) SQL Server will not allow me to mark it as persisted.
Anyway, here is my proposed solution for this:

首先，创建一个将计算任务ID的函数:

First, create a function that will calculate the task id:

CREATE FUNCTION dbo.GenerateTaskId
(
    @Row_Id int,
    @Issue_Id int,
    @Issue_Sub_Id int
)
RETURNS Int
AS
BEGIN

    RETURN 
    (
        SELECT COUNT(*)
        FROM dbo.Tasks
        WHERE Issue_Id = @Issue_Id
        AND Issue_Sub_ID = @Issue_Sub_ID
        AND Row_Id <= @Row_Id
    )
END
GO 

然后，使用任务ID作为计算列创建表:

Then, create the table with the task id as a computed column:

CREATE TABLE dbo.Tasks
(
    Row_Id [int] IDENTITY(1,1),
    Issue_ID [int] NOT NULL,
    Issue_Sub_ID [int] NOT NULL,
    Task_Id AS dbo.GenerateTaskId(Row_Id, Issue_Id, Issue_Sub_Id), 
    CONSTRAINT PK_Tasks PRIMARY KEY (Row_Id)
)
GO

现在，对其进行测试:

INSERT INTO Tasks VALUES
(12345, 1),
(12345, 1),
(12345, 1),
(12345, 2),
(12345, 2),
(67890, 2),
(67890, 2),
(67890, 2)

SELECT *
FROM Tasks

结果:

Row_Id  Issue_ID    Issue_Sub_ID    Task_Id
1       12345       1               1
2       12345       1               2
3       12345       1               3
4       12345       2               1
5       12345       2               2
6       67890       2               1
7       67890       2               2
8       67890       2               3

您可以在rextester上观看实时演示.

这篇关于类似于身份的列，但基于分组依据条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！