问题描述
在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:创建一列作为重复模式的身份的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!