T-SQL 生成唯一代码

Question

我有一个表联系人。

CREATE TABLE [dbo].[Contacts](
 [ContactId] [int] IDENTITY(1,1) NOT NULL,
 [ContactCode] [nvarchar](10) NOT NULL, --UNIQUE
 [FirstName] [nvarchar](24) NOT NULL,
 [MiddleName] [nvarchar](12) NULL,
 [LastName] [nvarchar](24) NOT NULL,
 CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED 
(
 [ContactId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

我希望能够从存储过程中生成 10 位联系代码，该存储过程应包含

First Two Digits of FirstName
First Digit of MiddleName(When MiddleName is not blank)
First Two or Three(When MiddleName is Blank) Digits of Last Name
Four digit number starting from zero.

示例。

John M. Denver = JOMDE-0001
John Denver    = JODEN-0001
John Denver    = JODEN-0002
Bob Dylan      = BODYL-0001

建议的解决方案可以是一个函数，可以在将值保存到表之前从存储过程内部调用该函数。

CREATE FUNCTION GetContactCode
(
 @FirstName nvarchar(24),
 @MiddleName nvarchar(12),
 @LastName nvarchar(24)
)
RETURNS nvarchar(10)
AS
BEGIN
 RETURN('CODE')
END

有人可以帮忙吗？

Answer 1

使用 RANKING 子句是一种选择。

;WITH q AS (
  SELECT  ContactID 
          , ContactCode = 
              UPPER(
                SUBSTRING(
                  SUBSTRING(FirstName, 1, 2)
                  + ISNULL(SUBSTRING(MiddleName, 1, 1), '')
                  + SUBSTRING(LastName, 1, 3)
                  , 1, 5)
                + '-')          
  FROM    Contacts
)
SELECT  c.*
        , q.ContactCode 
          + RIGHT(REPLICATE('0', 4) 
                  + CAST(
                      RANK() OVER (PARTITION BY q.ContactCode ORDER BY q.ContactID
                    ) AS VARCHAR(4))
                  , 4)
FROM    q INNER JOIN Contacts c ON c.ContactId = q.ContactID

Answer 2

这个函数应该可以解决问题

CREATE FUNCTION GetContactCode
(
 @FirstName nvarchar(24),
 @MiddleName nvarchar(12),
 @LastName nvarchar(24)
)
RETURNS nchar(10)
AS
BEGIN
 declare @contactcode nchar(10)

 select top 1 @contactcode = contactCode
 from contacts
 where
  contactcode like left(@FirstName,2)+
    case    
        when @MiddleName is null or @MiddleName = '' then left(@LastName,3) 
        else left(@MiddleName,1)+left(@LastName,2) 
    end+'%'
 order by contactCode desc

    select @Contactcode = 
    Upper(left(@FirstName,2)+
    case    
        when @MiddleName is null or @MiddleName = '' then left(@LastName,3) 
        else left(@MiddleName,1)+left(@LastName,2) 
    end) + '-' + 
    case 
        when @ContactCode is null then'0001' 
        else replace(str(cast(right(@ContactCode,4) as int)+1,4),' ','0') 
    end

    Return @ContactCode

END