SQL Server:创建一列作为重复模式的身份 [英] SQL Server : create a column as an Identity that repeats a pattern

问题描述

在SQL Server 2008 R2中，我知道如何创建使行保持唯一的主键列(在下面的示例中，column3)，但是我还需要一个自动填充重复的整数集(在示例中，column2)的列以下).

In SQL Server 2008 R2 I know how to create a primary key column that keeps my lines unique (column3 in my example below), but I also need a column that auto-fills a repeating set of integers (column2 in my example below).

我不知道如何创建这样的列.搜索表明可以使用重新播种"完成此操作，但不确定如何实际创建此列，或者甚至不确定是否可以自动执行此操作.

I don't know how to create a column like this. Searches suggest it can be done with "reseeding", but not sure how to actually a create this column or if its even possible to automatically do this.

我正在寻找类似的东西，每次创建新行时都会自动填充Column2:

I'm looking for something like this, with Column2 being auto-filled every time a new line is created:

Column1   Column2  Column3
name1     1        1
name2     2        2
name3     3        3
name4     4        4
name5     5        5
name6     1        6
name7     2        7
name8     3        8
name9     4        9
name10    5        10
name11    1        11
name12    2        12

推荐答案

您可以通过执行以下操作来使用持久列:

You could use a persisted column by doing something along these lines:

ALTER TABLE Table_Name ADD Column2 AS (Column3 % 5)

虽然我认为这将给您0-4而不是1-5，但它应该使您走上正确的道路，对Column3进行一些数学运算以创建您要查找的列.

While I think this will give you 0-4 instead of 1-5, it should put you on the right track to do some mathyness on Column3 to create the column you are looking for.

对于不断更新的答案(如果删除了行则更新)，您可以执行以下操作:

For a constantly updating answer that updates if rows are removed, you could do something like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.GetTableValue
(
    @Index int
)
RETURNS int
AS
BEGIN
    DECLARE @Result int
    SELECT @Result = COUNT(*) % 5
        FROM dbo.TABLE
        WHERE PrimaryKey < @Index

    RETURN @Result
END
GO

ALTER TABLE dbo.TableName ADD CalcValue AS (dbo.GetTableValue(PrimaryKey));

该查询尚未经过全面测试，但应该使您走上正确的道路.再一次，由于查询是在每次从数据库读取时执行的，这将损害更大数据库的性能.

That query isn't fully tested but should put you on the right path. Once again, this will hurt performance in a larger database due to the query executing on every read from the DB.

这篇关于SQL Server:创建一列作为重复模式的身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！