SQL Server MS SQL如何根据另一个字段添加自动增量字段

cl25kdpy  于 2023-01-20  发布在  其他
关注(0)|答案(1)|浏览(117)

例如,有一个表

int type
 int number
 int value

如何在将一个值插入到从1开始为不同类型编制索引的表中时执行此操作。

type 1 => number 1,2,3...
type 2 => number 1,2,3...

也就是说,它看起来像这样。
| 类型|数|价值|
| - ------|- ------|- ------|
| 1个|1个|- -|
| 1个|第二章|- -|
| 1个|三个|- -|
| 第二章|1个|- -|
| 1个|四个|- -|
| 第二章|第二章|- -|
| 三个|1个|- -|
| 六个|1个|- -|
| 1个|五个|- -|
| 第二章|三个|- -|
| 六个|第二章|- -|
特别感谢@Larnu。
因此,在我的例子中,最好的解决方案是为每种类型创建一个表。

c7rzv4ha

c7rzv4ha1#

正如我在评论中提到的,IDENTITYSEQUENCE都不支持使用另一列来表示它们应该使用什么“标识集”。您 * 可以 * 拥有多个SEQUENCE,它们可以用于单个表,但是,这不能扩展。例如,如果您被具体限制为2或3种类型,您可能会选择创建3个SEQUENCE对象。然后使用存储过程来处理INSERT语句。然后,当用户/应用程序需要INSERT数据时,他们调用该过程,该过程具有根据type列的参数值使用SEQUENCE的逻辑。
但是,正如前面提到的,这并不能很好地扩展。如果type的值的数量不确定,那么您就不能轻松地获得正确的SEQUENCE,并且处理type的新值也会很困难。在这种情况下,最好先使用IDENTITY,然后再使用VIEWVIEW将使用ROW_NUMBER来创建标识符,而IDENTITY则为您提供始终递增的值。

CREATE TABLE dbo.YourTable (id int IDENTITY(1,1),
                            [type] int NOT NULL,
                            number int NULL,
                            [value] int NOT NULL);
GO

CREATE VIEW dbo.YourTableView AS
    SELECT ROW_NUMBER() OVER (PARTITION BY [type] ORDER BY id ASC) AS Identifier,
           [type],
           number,
           [value]
    FROM dbo.YourTable;

然后,改为查询VIEW,而不是TABLE
如果需要色谱柱的一致性(我命名为identifier),您还需要确保(s)不能是表中的DELETE d。很可能是通过向定义为bit的表中添加IsDeleted列(0表示未删除,1表示已删除),然后您可以筛选到VIEW中的这些行:

CREATE VIEW dbo.YourTableView AS
    WITH CTE AS(
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY [type] ORDER BY id ASC) AS Identifier,
               [type],
               number,
               [value],
               IsDeleted
        FROM dbo.YourTable)
    SELECT id,
           Identifier,
           [type],
           number,
           [value]
    FROM CTE
    WHERE IsDeleted = 0;

如果需要,您甚至可以在VIEW上处理DELETEINSERTUPDATE将被隐式处理,因为它是一个可更新的VIEW):

CREATE TRIGGER trg_YourTableView_Delete ON dbo.YourTableView 
INSTEAD OF DELETE AS
BEGIN
    SET NOCOUNT ON;
    UPDATE YT
    SET IsDeleted = 1
    FROM dbo.YourTable YT
         JOIN deleted d ON d.id = YT.id;
END;
GO

db<>fiddle
为了完成,如果你想使用不同的SEQUENCE对象,它看起来像这样。注意这个 * 不 * 容易缩放。我必须为Type的每个值CREATE一个SEQUENCE。因此,对于一个小的,已知的,值的范围,这将是一个解决方案,但如果您最终要为type获得更大的价值或已经有了一个很大的范围,这最终不会很快成为可行的:

CREATE TABLE dbo.YourTable (identifier int NOT NULL,
                            [type] int NOT NULL,
                            number int NULL,
                            [value] int NOT NULL);

CREATE SEQUENCE dbo.YourTable_Type1
    START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE dbo.YourTable_Type2
    START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE dbo.YourTable_Type3
    START WITH 1 INCREMENT BY 1;

GO

CREATE PROC dbo.Insert_YourTable @Type int, @Number int = NULL, @Value int AS
BEGIN

    DECLARE @Identifier int;
    IF @Type = 1
        SELECT @Identifier = NEXT VALUE FOR dbo.YourTable_Type1;
    IF @Type = 2
        SELECT @Identifier = NEXT VALUE FOR dbo.YourTable_Type2;
    IF @Type = 3
        SELECT @Identifier = NEXT VALUE FOR dbo.YourTable_Type3;

    INSERT INTO dbo.YourTable (identifier,[type],number,[value])
    VALUES(@Identifier, @Type, @Number, @Value);

END;

相关问题