TEX自动增量

Question

我有一个表名: my_table

列: ID、名称、部门(部门为下拉列表，如下所示：

AOD
CRM
RMD

现在，我想知道，当自动增量产生时，它将取决于部门，如下所示。

如果部门AOD，那么ID将生成如下：

AOD00001
AOD00002
AOD00003

如果部门CRM，那么ID将生成如下：

CRM00001
CRM00002
CRM00003

如果部门RMD，那么ID将生成如下：

RMD00001
RMD00002
RMD00003

或

AOD00001
CRM00002
AOD00003
RMD00004
CRM00005

Answer 1

我建议要么使用一个额外的表，要么在当前的Departments表中添加一个新的计数器字段。

然后构建一个存储过程或使用一个前置插入触发器，在每次插入新记录时悄悄地添加下一个ID。

CREATE TABLE [dbo].[DepartmentSeq]
(
    [department]        varchar(10)     NOT NULL,
    [sequence]          int             NOT NULL,

    CONSTRAINT [PK_DepartmentSeq] 
        PRIMARY KEY CLUSTERED ([department] ASC)
) 
ON [PRIMARY];
GO

--= SP [sp_DeparmentSeq_next]
--------------------------------------------------
CREATE PROCEDURE [dbo].[sp_DeparmentSeq_next]
(
@department varchar(10),
@next_sequence int OUT
)
AS
BEGIN

    DECLARE @sequence int;
    SET @sequence= 0;
    SET @next_sequence = 0;

    BEGIN TRY

        SELECT   @sequence = [sequence]
        FROM     [dbo].[DepartmentSeq] WITH (UPDLOCK)
        WHERE    [department] = @department;

        IF @@ROWCOUNT > 0
        BEGIN 
            SET @sequence= @sequence + 1;

            UPDATE [dbo].[DepartmentSeq]
            SET    sequence = @sequence
            WHERE  [department] = @department;
        END
        ELSE
        BEGIN
            SET @sequence = 1;

            INSERT INTO [dbo].[DepartmentSeq] (department, sequence)
            VALUES      (@department, @sequence);
        END

        SET @next_sequence = @sequence;

    END TRY
    BEGIN CATCH

        DECLARE @en INT, @es INT, @ep SYSNAME, @el INT, @em NVARCHAR(2048);
        SET     @en = ERROR_NUMBER();
        SET     @es = ERROR_SEVERITY();
        SET     @ep = ERROR_PROCEDURE()
        SET     @el = ERROR_LINE();
        SET     @em = ERROR_MESSAGE();
        -- EXEC [dbo].[vsp_log_errors_insert] @ep, @en, @em, @es, @el;
        RAISERROR(@em, @es, @el);
        RETURN @en;

    END CATCH

    RETURN 0;

END
GO

作为第二种选择，如果部门数量有限，并且取决于您使用的Server版本，则可以为每个部门使用一个序列。

Answer 2

我建议使用@McNets方法来实现您的目标，但是，我喜欢提供另一种方法来实现它。

使用CTE的

解决方案您可以使用公共表表达式来使用类似的查询生成您要查找的id WITH CTE_1 AS (SELECT * , ROW_NUMBER() OVER(PARTITION BY [department] ORDER BY [ID]) as rcount FROM #TblTemp) SELECT [department] + RIGHT('00000' + CAST(rcount as varchar(5)), 5) FROM CTE_1

测试

--Initialize Table

CREATE TABLE #TblTemp(ID INT IDENTITY(1,1), [name] VARCHAR(50), department VARCHAR(3))

INSERT INTO #TblTemp([name],department) 

SELECT 'aa','AOD'
UNION ALL
SELECT 'bb','AOD'
UNION ALL
SELECT 'cc','CRM'
UNION ALL
SELECT 'dd','CRM'
UNION ALL
SELECT 'ee','AOD'
UNION ALL
SELECT 'ff','RMD'
UNION ALL
SELECT 'gg','CRM'

Select * From #TblTemp;

--Generate Id using CTE
    WITH CTE_1 AS (SELECT * , ROW_NUMBER() OVER(PARTITION BY [department] ORDER BY [ID]) as rcount FROM #TblTemp)
    SELECT [name] , [department] + RIGHT('00000' + CAST(rcount as varchar(5)), 5) as [NewID] FROM CTE_1

结果