Sql Server-实用技巧-流水号生成

代码如下

IF exists(SELECT * FROM sysobjects WHERE name='GetSerialNumber' and Type='FN')
   DROP FUNCTION [dbo].[GetSerialNumber]
Go
/* =======================================
创 建 人：Lemon
创建日期：2020-05-14
功能描述：获取流水号,格式:标识+年月日+四位数序号(该年月日下数据的数量),需要有数据创建时间CDate
            如果是新增则传入主键PrimaryKeyID为0,生成位新序号
            如果是原有数据编辑则传入主键PrimaryKeyID,在CDate下根据主键进行排序获取四位数序号
            如有需要可以将对应的TableName 和 PrimaryKeyName 变为需要的表名和主键名，CDate修改为对应的日期
单元名称： GetSerialNumber
======================================= */
Create FUNCTION [dbo].[GetSerialNumber](@PrimaryKeyID int,@Reamrk Nvarchar(50))  
    RETURNS nvarchar(1000)  
AS
    BEGIN
        DECLARE @SerialNumber NVARCHAR(1000)    
        IF isnull(@PrimaryKeyID,0) <>0
            Begin
                SELECT @SerialNumber = isnull(@Reamrk,'')
                                        + convert(char(8), (SELECT isnull(CDate, GetDate()) FROM [dbo].[TableName] WHERE PrimaryKeyName = @PrimaryKeyID), 112) 
                                        + RIGHT('00000000000' 
                                        + CAST((SELECT RowID 
                                                FROM (
                                                    SELECT ROW_NUMBER() OVER (ORDER BY PrimaryKeyName) AS RowID, *
                                                    FROM [dbo].[TableName]
                                                    WHERE convert(char(8), CDate, 112) = convert(char(8), (SELECT isnull(CDate, GetDate())FROM [dbo].[TableName]WHERE PrimaryKeyName = @PrimaryKeyID), 112)) a
                                                WHERE PrimaryKeyName = @PrimaryKeyID
                                        ) AS varchar(100)), 4)
            End
        ELSE
            Begin
                SELECT @SerialNumber = isnull(@Reamrk,'') 
                            + convert(char(8), getdate(), 112)  
                            + RIGHT('00000000000' + CAST((SELECT COUNT(1) + 1 FROM [dbo].[TableName] WHERE convert(char(8), CDate, 112) = convert(char(8), getdate(), 112) ) AS varchar(100)), 4)
            End


        RETURN isnull(@SerialNumber,'')
    End
Go
select SerialNumber=dbo.GetSerialNumber(PrimaryKeyName,'Demo') from TableName