问使用SQL创建唯一标识符

EN

CREATE TABLE Sequences
(
OfficeCode char(1) primary key,
SequenceNumber int NOT NULL DEFAULT (0)
)

INSERT INTO Sequences(OfficeCode)
SELECT 'P' UNION ALL SELECT 'C' UNION ALL SELECT 'A'

GO

CREATE PROC dbo.GetSequence
@OfficeCode char(1),
@val AS int OUTPUT,
@n as int =1
AS
UPDATE dbo.Sequences 
SET @val = SequenceNumber = SequenceNumber + @n
WHERE OfficeCode = @OfficeCode;

SET @val = @val - @n + 1; 

GO


DECLARE @val  int

EXEC dbo.GetSequence 'C', @val output
select @val

declare @records table (id int identity(1,1), CreationDate datetime, Name varchar(50), Section char(1), FileID varchar(10))

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:57:49', 'abc','p'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:57:50', 'def','p'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:00', 'ghi','c'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:50', 'jkl','d'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:51', 'mno','c'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:52', 'pqr','p'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:53', 'def','p'

update @records
set FileID=a.FileID
from 
(
    select id,
    Section + cast(row_number() over (partition by Section order by CreationDate, Section) as varchar(10)) as FileID
    from @records
) a
    inner join @records b
        on a.id=b.id

select * from @records