Sql生成不重复的数字

最新推荐文章于 2021-03-23 23:11:06 发布

转载最新推荐文章于 2021-03-23 23:11:06 发布 · 262 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zjflove/p/5411591.html

文章标签：

#数据库

-- =============================================
-- Author:TUZI
-- Create date: 2016.4.18
-- Description:生成活动邀约码(活动编码+Code是唯一)

/*
 exec sp_generateInvitecode  
 @CampaignCode='test' ,@Length=10,@Quantity=100000
 
*/

-- =============================================
ALTER PROCEDURE sp_generateInvitecode(@CampaignCode nvarchar(50)
                                      ,--所属活动编码
                                       @Length int
                                      ,--Code的长度
                                       @Quantity int--Code的数量
)
AS
BEGIN
    CREATE TABLE #t(Code nvarchar(50));
    CREATE UNIQUE INDEX idx_union_code ON #t(Code)

    DECLARE @batchNum nvarchar(50);
    SET @batchNum = REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(nvarchar(19),GETDATE(),121),' ',''),'-',''),':',''),'.','')
    --如果活动进行中,需要继续生成邀请码，就得排除已经存在于表中的码

    SELECT Code INTO #existsCode
      FROM  XXX
      WHERE CampaignCode = @CampaignCode

    DECLARE @runQ int = 0;
    WHILE @runQ < @Quantity
        BEGIN
            INSERT INTO #t
            SELECT TOP 10000 LEFT(CAST(LEFT('10000000000',@Length)AS bigint) + ABS(CHECKSUM(NEWID())),@Length)
              FROM syscolumns c1,syscolumns c2;

            DELETE #t
            WHERE Code IN(SELECT Code
                            FROM #existsCode);
            SELECT @runQ = COUNT(DISTINCT Code)
              FROM #t;
        END

    INSERT INTO  XXX(CampaignCode
                            ,Code
                            ,BatchNum
                            ,createTime)
    SELECT TOP (@Quantity)@CampaignCode
                         ,Code
                         ,@batchNum
                         ,GETDATE()
      FROM #t
    DROP TABLE #t;
END
GO

转载于:https://www.cnblogs.com/zjflove/p/5411591.html