我写了一份程序，可以申请身份证。
有不同类型的身份证（红色、蓝色、绿色）。
在申请过程中，程序应生成身份号码。号码的范围取决于所请求的卡片类型。
Red Card: 1 - 50000
Blue Card: 50001 - 100000
Green Card: 100001 - 150000
如果我在系统中添加新的身份证，那么序列应该自动为该新添加的身份证创建一个新的数字范围。这些数字不应重复使用，每个数字只能使用一次。
如何实现这一点？有没有人能帮我解决这个问题？
在MS SQL Server 2008中创建序列
5
如果我在系统中添加新的身份证，那么序列应该自动为该新添加的身份证创建一个新的数字范围。这些数字不应重复使用，每个数字只能使用一次。
如何实现这一点？有没有人能帮我解决这个问题？
- Paks
5
1第50001个红牌请求应该发生什么？ - Cristian Lupascu
5解决方案是否需要并发执行，或者我们可以假设数据库请求由应用程序层串行处理？也就是说，对于数据库，我们可以假设应用程序层是单线程的吗？ - Bohemian
1你是否假设你永远不会拥有超过50k张任何类型的卡片，并且你永远不会拥有比（MAXIMUM_INT_VALUE）/50k更多的卡片？ - nimdil
我们能否创建一个额外的表来保存所有卡牌颜色和范围？ - Bohemian
请参阅https://dev59.com/t1rUa4cB1Zd3GeqPm8O4。 - flup
10个回答
2
您可以使用“代替插入触发器”来实现此功能。
请注意，最好在卡片的列
另外，请注意，您只能为每种类型插入50000条记录。您可以使用不同的种子，例如1表示“RED”，2表示“BLUE”等，并为100个类型的卡片保留位置。
create table Cards_Types (Color nvarchar(128) primary key, Start int);
create table Cards (ID int primary key, Color nvarchar(128));
insert into Cards_Types
select 'RED', 0 union all
select 'BLUE', 50000 union all
select 'GREEN', 100000;
create trigger utr_Cards_Insert on Cards
instead of insert as
begin
insert into Cards (id, Color)
select
isnull(C.id, CT.Start) + row_number() over(partition by i.Color order by i.id),
i.Color
from inserted as i
left outer join Cards_Types as CT on CT.Color = i.Color
outer apply (
select max(id) as id
from Cards as C
where C.Color = i.Color
) as C
end
它允许您一次性插入多行：
insert into Cards (Color)
select 'GREEN' union all
select 'GREEN' union all
select 'RED' union all
select 'BLUE'
请注意，最好在卡片的列
Color, ID 上建立索引。另外，请注意，您只能为每种类型插入50000条记录。您可以使用不同的种子，例如1表示“RED”，2表示“BLUE”等，并为100个类型的卡片保留位置。
create table Cards_Types (Color nvarchar(128) primary key, Start int);
create table Cards (ID int primary key, Color nvarchar(128));
insert into Cards_Types
select 'RED', 1 union all
select 'BLUE', 2 union all
select 'GREEN', 3;
create trigger utr_Cards_Insert on Cards
instead of insert as
begin
insert into Cards (id, Color)
select
isnull(C.id, CT.Start - 100) + row_number() over(partition by i.Color order by i.id) * 100,
i.Color
from inserted as i
left outer join Cards_Types as CT on CT.Color = i.Color
outer apply (
select max(id) as id
from Cards as C
where C.Color = i.Color
) as C
end;
这样，“RED”的ID总是以1结尾，“BLUE”的ID以2结尾，依此类推。
- Roman Pekar
2
从设计的角度来看，我强烈不建议将额外逻辑编码到标识符中，例如为特定范围分配卡片颜色。我宁愿使用处理唯一性和并发性良好的IDENTITY列，使ID完全代理，并在另一个属性中存储给定ID的卡片颜色信息。可能会在该附加属性上创建一个索引以检索给定颜色的记录。
此外，要考虑如果红卡的所有者要求更改为蓝色卡需要什么？在范围内，为保留颜色分配，您需要创建一个新的ID，也许在其他地方存储旧到新的ID序列的信息。如果有人多次更改，会怎样呢？使用代理ID，您可以始终具有一个ID，以便能够通过整个历史跟踪同一人，并且可能只需向表格添加日期信息以按顺序排序更改。这只是一个简单场景的例子。
此外，要考虑如果红卡的所有者要求更改为蓝色卡需要什么？在范围内，为保留颜色分配，您需要创建一个新的ID，也许在其他地方存储旧到新的ID序列的信息。如果有人多次更改，会怎样呢？使用代理ID，您可以始终具有一个ID，以便能够通过整个历史跟踪同一人，并且可能只需向表格添加日期信息以按顺序排序更改。这只是一个简单场景的例子。
- miazo
1
您可以利用SQL Server的IDENTITY机制来实现此目的，因为它易于使用且能很好地处理并发。
更具体地说，您可以创建三个仅包含自增的标识符列Id的表，使用以下脚本：
create table RedCardIds(Id int identity(1, 1) primary key)
create table BlueCardIds(Id int identity(50001, 1) primary key)
create table GreenCardIds(Id int identity(100001, 1) primary key)
GO
三个表的标识值都设置为与您的区间下限匹配。然后，对于每个请求，您将插入到适当的表中，并使用OUTPUT子句获取新生成的标识值。例如，如果请求是红卡，则可以编写以下代码：
insert RedCardIds
output inserted.Id
default values
它会输出：
Id
-----------
1
(1 row(s) affected)
在下一次运行时它将返回
2，以此类推。同样，第一个请求蓝卡将触发该语句：
insert BlueCardIds
output inserted.Id
default values
带有结果的：
Id
-----------
500001
(1 row(s) affected)
- Cristian Lupascu
4
我认为创建表并不是一个好的解决方案。卡片可以动态地创建。因此，不仅仅是三张卡片。在我的系统中，我可以创建新的卡片，所以我需要一个过程/序列，也会自动为新插入的卡片创建一个新范围。 - Paks
@Paks 当添加/删除卡片时，表格也可以动态创建/删除。 - Cristian Lupascu
LoL，他们可以这样做，但绝对不应该:D - nimdil
我喜欢它。最简单的事情可能会起作用。一眼就能看出来。 - flup
0
有很多答案，但我会加上我的两分钱。请注意，我假设我在您原始帖子的评论中所写的内容：
create table cardTypes(cardTypeName varchar(100) primary key, [50kSlot] int unique)
create table cards (identificationNumber bigint primary key);
--add slot if needed
declare @cardToBeAdded varchar(100) = 'green'
declare @tbl50kSlot table (i int)
merge into cardTypes as t
using (select @cardToBeAdded as newCard) as s
on t.[cardTypeName] = s.newCard
when not matched by target then
insert (cardTypeName, [50kSlot]) values (s.newCard, isnull((select max([50kSlot]) + 1 from cardTypes),1))
when matched then
update set [50kSlot] = [50kSlot]
output inserted.[50kSlot] into @tbl50kSlot;
declare @50kSlot int = (Select i from @tbl50kSlot)
insert into cards (identificationNumber) values (isnull(
(select max(identificationNumber)+1 from cards where identificationNumber between ((@50kSlot-1)*50000+1) and @50kSlot*50000),
(@50kSlot-1)*50000+1)
)
当然，您需要向卡表添加一些实际数据。请注意，如果存在足够有效的索引，则最后一个查询可以相对快速地执行。如果性能存在问题，则可能值得绕过对identificationNumber进行索引。例如，如果您将拥有大量行，请考虑在此列上创建过滤索引。
或者，您可以将maxInt保留在cardTypes表中，并使合并表略微复杂化。缺点是，如果查询之间出现某种错误，则永远不会使用该数字，因此我的解决方案使序列紧密。
- nimdil
0
MS SQL Server 2008架构设置：
CREATE TABLE Table1
([color] varchar(10), [id] int)
;
INSERT INTO Table1
([color], [id])
VALUES
('Red',(select isnull(case when (max(id)/50000)%3 = 1 and
max(id)%50000 = 0 then max(id)+100000 else
max(id) end,0)+1
from Table1 where color = 'Red'));
INSERT INTO Table1 ([color], [id]) VALUES ('Red',50000);
INSERT INTO Table1
([color], [id])
VALUES
('Red',(select isnull(case when (max(id)/50000)%3 = 1 and
max(id)%50000 = 0 then max(id)+100000 else
max(id) end,0)+1
from Table1 where color = 'Red'));
INSERT INTO Table1
([color], [id])
VALUES
('Blue',(select isnull(case when (max(id)/50000)%3 = 2 and
max(id)%50000 = 0 then max(id)+100000 else
max(id) end,50000)+1
from Table1 where color = 'Blue'));
INSERT INTO Table1
([color], [id])
VALUES
('Green',(select isnull(case when (max(id)/50000)%3 = 0 and
max(id)%50000 = 0 then max(id)+100000 else
max(id) end,100000)+1
from Table1 where color = 'Green'));
查询 1:
SELECT *
FROM Table1
结果:
| COLOR | ID |
|-------|--------|
| Red | 1 |
| Red | 50000 |
| Red | 150001 |
| Blue | 50001 |
| Green | 100001 |
- Fabien TheSolution
0
这是我对挑战的贡献。不需要额外的表格，应该是并发安全的，并且可以处理批量更新。虽然可能不是最快的，但它能够正常工作。它基本上将要插入的行复制到一个单独的表中，按颜色创建ID，最后将所有内容移动到目标表中。
我在
Create Trigger Trg_CreateID ON dbo.Cards instead of insert
as
begin
set nocount on
-- declare a working table holding intermediate results
declare @Tmp Table (cardID int, cardColor char(1), cardNumber char(20))
-- copy the data to be inserted in our working table
insert into @Tmp select * from inserted
declare @Id int
-- fill in the Id's once per color
select @Id=coalesce (max(cardID),0) from dbo.Cards where cardColor='Red'
update @Tmp set cardID = @Id, @Id=@id+1 where cardColor='Red'
select @Id=coalesce(max(cardID),50000) from dbo.Cards where cardColor='Blue'
update @Tmp set cardID = @Id, @Id=@id+1 where cardColor='Blue'
select @Id=coalesce(max(cardID),100000) from dbo.Cards where cardColor='Gree'
update @Tmp set cardID = @Id, @Id=@id+1 where cardColor='Green'
-- do the actual insert here
insert into dbo.Cards select * from @tmp
end
它假设有一个名为Cards的表格，就像这样
CREATE TABLE [dbo].[Cards]
(
[cardID] [int] NOT NULL,
[cardColor] [char](1) NOT NULL,
[cardNumber] [char](20) NOT NULL
) ON [PRIMARY]
我在
cardID列中添加了一个约束条件，以允许在插入语句中省略它。ALTER TABLE [dbo].[Cards]
ADD CONSTRAINT [DF_Cards_cardID] DEFAULT ((0)) FOR [cardID]
- alzaimar
0
编辑 #1：我更新了触发器（IF UPDATE）、存储过程和最后两个示例。
CREATE TABLE dbo.CustomSequence
(
CustomSequenceID INT IDENTITY(1,1) PRIMARY KEY,
SequenceName NVARCHAR(128) NOT NULL, -- or SYSNAME
UNIQUE(SequenceName),
RangeStart INT NOT NULL,
RangeEnd INT NOT NULL,
CHECK(RangeStart < RangeEnd),
CurrentValue INT NULL,
CHECK(RangeStart <= CurrentValue AND CurrentValue <= RangeEnd)
);
GO
CREATE TRIGGER trgIU_CustomSequence_VerifyRange
ON dbo.CustomSequence
AFTER INSERT, UPDATE
AS
BEGIN
IF (UPDATE(RangeStart) OR UPDATE(RangeEnd)) AND EXISTS
(
SELECT *
FROM inserted i
WHERE EXISTS
(
SELECT * FROM dbo.CustomSequence cs
WHERE cs.CustomSequenceID <> i.CustomSequenceID
AND i.RangeStart <= cs.RangeEnd
AND i.RangeEnd >= cs.RangeStart
)
)
BEGIN
ROLLBACK TRANSACTION;
RAISERROR(N'Range overlapping error', 16, 1);
END
END;
GO
--TRUNCATE TABLE dbo.CustomSequence
INSERT dbo.CustomSequence (SequenceName, RangeStart, RangeEnd)
SELECT N'Red Card', 1, 50000 UNION ALL
SELECT N'Blue Card', 50001, 100000 UNION ALL
SELECT N'Green Card', 100001, 150000;
GO
-- Test for overlapping range
INSERT dbo.CustomSequence (SequenceName, RangeStart, RangeEnd)
VALUES (N'Yellow Card', -100, +100);
GO
/*
Msg 50000, Level 16, State 1, Procedure trgIU_CustomSequence_VerifyRange, Line 20
Range overlapping error
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
GO
-- This procedure tries to reserve
CREATE PROCEDURE dbo.SequenceReservation
(
@CustomSequenceID INT, -- You could use also @SequenceName
@IDsCount INT, -- How many IDs do we/you need ? (Needs to be greather than 0)
@LastID INT OUTPUT
)
AS
BEGIN
DECLARE @StartTranCount INT, @SavePoint VARCHAR(32);
SET @StartTranCount = @@TRANCOUNT;
IF @StartTranCount = 0 -- There is an active transaction ?
BEGIN
BEGIN TRANSACTION -- If not then it starts a "new" transaction
END
ELSE -- If yes then "save" a save point -- see http://technet.microsoft.com/en-us/library/ms188378.aspx
BEGIN
DECLARE @ProcID INT, @NestLevel INT;
SET @ProcID = @@PROCID;
SET @NestLevel = @@NESTLEVEL;
SET @SavePoint = CONVERT(VARCHAR(11), @ProcID) + ',' + CONVERT(VARCHAR(11), @NestLevel);
SAVE TRANSACTION @SavePoint;
END
BEGIN TRY
UPDATE dbo.CustomSequence
SET @LastID = CurrentValue = ISNULL(CurrentValue, 0) + @IDsCount
WHERE CustomSequenceID = @CustomSequenceID;
IF @@ROWCOUNT = 0
RAISERROR(N'Invalid sequence', 16, 1);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @StartTranCount = 0
BEGIN
ROLLBACK TRANSACTION;
END
ELSE -- @StartTranCount > 0
BEGIN
ROLLBACK TRANSACTION @SavePoint
END
DECLARE @ErrorMessage NVARCHAR(2048), @ErrorSeverity INT, @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
END;
GO
SELECT * FROM dbo.CustomSequence;
GO
-- Example usage #1
DECLARE @LastID INT;
EXEC dbo.SequenceReservation
@CustomSequenceID = 1, -- Red Card
@IDsCount = 2, -- How many IDs ?
@LastID = @LastID OUTPUT;
SELECT @LastID - 2 + 1 AS [FirstID], @LastID AS [LastID];
GO
-- Example usage #2
DECLARE @LastID INT;
EXEC dbo.SequenceReservation
@CustomSequenceID = 1, -- Red Card
@IDsCount = 7, -- How many IDs ?
@LastID = @LastID OUTPUT;
SELECT @LastID - 7 + 1 AS [FirstID], @LastID AS [LastID];
SELECT * FROM dbo.CustomSequence;
GO
结果：
CustomSequenceID SequenceName RangeStart RangeEnd CurrentValue
---------------- ------------ ----------- ----------- ------------
1 Red Card 1 50000 9
2 Blue Card 50001 100000 NULL
3 Green Card 100001 150000 NULL
- Bogdan Sahlean
2
为什么输出参数称为@FromID？它返回序列的当前值 - http://sqlfiddle.com/#!3/56f4d/2 - Roman Pekar
1为什么需要所有这些事务，你只有一个语句，如果implicit_tranactions打开的话，它本来就会在事务中（执行）。对于OP如何在调用过程后获取实际ID的一点解释也会很好。目前代码对我来说看起来非常复杂，并且它并没有显示将卡插入Cards表中。 - Roman Pekar
0
理想情况下，您需要维护一个表来存储这些信息。
这不是一个完整的工作。显然，你需要做一些错误处理来检查边界限制等。
CardCategry MinNumber MaxNumber RunningNumber
然后，您可以编写一个存储过程以获取下一个数字并将卡类别作为参数传递。示例查询如下所示。
SELECT @count=count(RunningNumber)
FROM IdentificationTable
WHERE CardCategry=@param
IF (@count=1)
SELECT @RunningNumber=RunningNumber
FROM IdentificationTable
WHERE CardCategry=@param
ELSE
SELECT TOP 1 @min=MinNumber,@max=MaxNumber
FROM IdentificationTable
ORDER BY MinNumber DESC
INSERT INTO IdentificationTable VALUES (@param,@max+1,@max+(@max-@min),1)
SET @RunningNumber=1
RETURN @RunningNumber
这不是一个完整的工作。显然，你需要做一些错误处理来检查边界限制等。
- Chamal
0
我会尝试这样做：
declare @cat2start int = 50000
declare @cat3start int = 100000
declare @catName varchar(10) = 'Red'
if @catName = 'Green'
begin
select (max(cardnumber) + 1) as [This is the next number]
from yourTable
where
cardnumber < @cat2start
end
if @catName = 'Blue'
begin
select (max(cardnumber) + 1) as [This is the next number]
from yourTable
where
cardumber >= @cat2start and cardnumber < @cat3start
end
if @catName = 'Red'
begin
select (max(cardnumber) + 1) as [This is the next number]
from yourTable
end
- Julius B
-1
*这个解决方案适用于单行插入，对于多行插入的并发需要采用不同的方法。更多细节请参见评论 *
如果没有创建表的选项，那么可以使用触发器来代替（类似于Oracle中的before触发器）。
在触发器内部使用特定条件来设置
Identity列的范围。以下是一个示例，展示如何实现您的解决方案。
表格
CREATE TABLE REQUEST_TABLE(
REQ_ID numeric(8, 0) NOT NULL,
REQ_COLOR VARCHAR(30) NOT NULL
); -- I have used this sample table
代替触发器
CREATE TRIGGER tg_req_seq ON REQUEST_TABLE
INSTEAD OF INSERT AS
DECLARE @REQ_ID INT
DECLARE @REQ_COLOR VARCHAR(30)
DECLARE @REQ_START INT
BEGIN
SELECT @REQ_COLOR= (SELECT ISNULL(REQ_COLOR,'NA') FROM INSERTED)
SELECT @REQ_START = (SELECT CASE WHEN @REQ_COLOR = 'Red' THEN 0
WHEN @REQ_COLOR = 'Blue' THEN 50000
ELSE 100000 END)
SELECT @REQ_ID = ISNULL(MAX(REQ_ID),@REQ_START)+1 FROM REQUEST_TABLE
WHERE REQ_COLOR = @REQ_COLOR
INSERT INTO REQUEST_TABLE (REQ_ID,REQ_COLOR)
VALUES (@REQ_ID,@REQ_COLOR)
END;
现在执行了一些插入语句
INSERT INTO REQUEST_TABLE VALUES(NULL,'Red');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Red');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Red');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Blue');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Blue');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Blue');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Yellow');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Yellow');
INSERT INTO REQUEST_TABLE VALUES(NULL,'Yellow');
我已经在SqlFiddle中添加了相同的结果。如果我漏掉了什么，请告诉我。
编辑
更新Fiddle以满足灵活的要求。
- Pratik
10
@Paks 如果卡牌范围已知，则只需修改 case 语句并添加自己的范围即可。如果卡片未配置，则 else 条件将处理该情况。 - Pratik
1@Paks 注意，此解决方案仅适用于单行插入，并且这不是并发的 - 您可以在
select @REQ_ID ...和insert into REQUEST_TABLE...之间添加 waitfor '00:00:05'，然后运行两个相同类型的卡片的并发插入。 - Roman Pekar@Paks 我已经更新了 Fiddle，以便可以从表格中提取卡片及其值。 - Pratik
2@Steve，不仅如此，如果在“select @REQ_ID”和“insert”之间存在一些延迟，那么很容易出现重复插入“@REQ_ID”。我不知道为什么OP选择了这个答案。 - Roman Pekar
2@Pratik 所以基本上，如果我用“触发器而不是”这样的词来写答案，它应该被视为一个答案吗？在SO上有很多人知道触发器，但我确定他们没有像这样发布一个完全无用的解决方案。我甚至不确定我的解决方案是否具有并发稳定性，但我至少已经测试过了，如果有人能创建一个更好的解决方案，我会很高兴。但是你的解决方案显然很容易破坏，并且仅适用于单行插入。我不会对其进行投票，但OP接受了这个答案，我肯定不希望其他人使用它。 - Roman Pekar
显示剩余5条评论
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接