SQL生成subversion号(Series Generation)

Question

下面是我的表架构：

CREATE TABLE [dbo].[Sample](
    [BoardId] [int] IDENTITY(1,1) NOT NULL,
    [UserCode] [nvarchar](20) NOT NULL,
    [Desc] [nvarchar](100) NOT NULL,
    [Version] [int] NOT NULL,
    [ParentId] [int] NULL,
    [CreatedOn] [datetime] NOT NULL,
    [ModifiedOn] [datetime] NOT NULL)

表的简短说明，用户可以选择保存“描述”用户信息为每个用户作为一个单独的版本每次。我想为每个正在创建的subVersion生成一个版本号。parentId字段保存要为其创建subversion的主键ID。例如，下面的一些记录。

boardID        UserCode     Desc       Version   ParentID  CreatedOn       ModifiedOn
1              12343jhgs    blah blah    1          0        ---            ---
2              12343jhgs    blah blah    1          1        ---            ---
3              12343jhgs    blah blah    2          1        ---            ---
4              73627ggjh    lol lol ol   1          0        ---            ---

希望你明白了，对于创建的每个subversion，我想生成一个递增的版本号。

Answer 1

基于您的样本数据的示例。我假设具有相同ParentId的boardId实例的版本号由插入顺序决定。

EDIT -重新阅读你的描述和评论我意识到第一个版本的父母和第一个孩子是故意的：

DECLARE @Sample TABLE 
(
    [BoardId] [INT] NOT NULL,
    [UserCode] [NVARCHAR](20) NOT NULL,
    [Desc] [NVARCHAR](100) NOT NULL,
    [ParentId] [INT] NULL,
    [CreatedOn] [DATETIME] ,
    [ModifiedOn] [DATETIME]
)

INSERT @Sample
(boardID,UserCode,[Desc],ParentID)
VALUES
(1,'12343jhgs','blah blah',0),
(2,'12343jhgs','blah blah',1),
(3,'12343jhgs','blah blah',1),
(4,'73627ggjh','lol lol ol',0)


;WITH recCTE
AS
(
    SELECT  boardID,UserCode,[Desc],ParentID, BoardID AS TopParent, 0 as versionModifier
    FROM    @Sample
    WHERE   ParentId = 0

    UNION ALL

    SELECT  s.boardID,s.UserCode,s.[Desc],s.ParentID, r.TopParent, -1 as versionModifier
    FROM    @Sample AS s 
    JOIN    recCTE AS r 
    ON      r.BoardId = s.ParentId
)
SELECT  boardID,UserCode,[Desc],ParentID, BoardID, 
        ROW_NUMBER() OVER (PARTITION BY TopParent
                            ORDER BY    boardId
                          )   + versionModifier AS [Version]
FROM recCTE