考虑租户ID的自动递增

本文介绍了考虑租户ID的自动递增的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用SQL Server，需要参考租户ID保留一个会计编号.

I'm using SQL Server and need to keep an accounting number with reference to the tenant id.

我不能使用自动增量ID，因为它是顺序递增的，并且在客户端应用程序中存在间隙.

I cannot use the auto increment id because it is sequentially increments and has gaps in the client applications.

我无法使用计算列，因为不允许使用像Max这样的聚合函数

I couldn't use computed columns as aggregate functions like Max is not allowed

什么是最好的方法?

推荐答案

您还可以在插入数据时进行处理，例如:

You also can process when insert data, For example:

insert into table1(ID,TenantId,PaymentId) select 6,2,isnull(max(PaymentId)+1,1) from table1 where TenantId=2 group by TenantId

如果要使用触发器，这是一个示例，在此示例中，即使您在插入数据时指定了PaymentId，该触发器也会重新计算PaymentId

If you want to use trigger,This is a sample, In ths sample, even you specify a PaymentId when inserting data, this trigger also recalculating the PaymentId

DROP TABLE table1 CREATE TABLE Table1(ID INT IDENTITY(1,1),TenantId INT ,PaymentId INT) CREATE TRIGGER trg_UpdatePaymentId ON dbo.TABLE1 AFTER INSERT AS BEGIN SET NOCOUNT ON; UPDATE t SET t.PaymentId=a.rn FROM dbo.TABLE1 AS t INNER JOIN ( SELECT i.ID,(ISNULL(c.MaxPaymentId,0)+ ROW_NUMBER()OVER(PARTITION BY TenantId ORDER BY ID)) AS rn FROM Inserted AS i OUTER APPLY( SELECT MAX(tt.PaymentId) AS MaxPaymentId FROM Table1 AS tt WHERE tt.TenantId=i.TenantId AND NOT EXISTS(SELECT 0 FROM Inserted AS ii WHERE ii.ID=tt.ID) ) AS c ) AS a ON a.ID=t.ID END GO INSERT INTO table1(TenantId)VALUES(1),(2),(1),(1) SELECT * FROM dbo.TABLE1

ID TenantId PaymentId ----------- ----------- ----------- 1 1 1 2 2 1 3 1 2 4 1 3