怎么样将sql生成6为随机数？
-------产生六位的随机数
declare @RandNumber uniqueidentifier
declare @Number nvarchar(10)
set @RandNumber = NewID()
--------MD5函数
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_ConvertToWordArray]') AND xtype IN(N'FN', N'IF', N'TF')) DROP FUNCTION [dbo].[MD5_ConvertToWordArray] GO /***************************************************************************** * Name: MD5_ConvertToWordArray * Description: MD5_ConvertToWordArray *****************************************************************************/ CREATE FUNCTION dbo.MD5_ConvertToWordArray( @sOrigMessVARCHAR(8000)= '' ) RETURNS @tWordArray TABLE([ID] INT IDENTITY(0,1),[Word] INT) WITH ENCRYPTION AS BEGIN IF @sOrigMess IS NULL SET @sOrigMess = '' DECLARE @iLenOfMessINT DECLARE @iWordArrayLenINT DECLARE @iPosOfWordINT DECLARE @iPosOfMessINT DECLARE @iCountOfWordINT SET @iLenOfMess = LEN(@sOrigMess) SET @iWordArrayLen = ((@iLenOfMess + 8)/64 + 1) * 16 SET @iCountOfWord = 0 WHILE(@iCountOfWord<@iWordArrayLen) BEGIN INSERT INTO @tWordArray([Word]) VALUES(0) SET @iCountOfWord = @iCountOfWord + 1 END SELECT @iPosOfMess = 0, @iPosOfWord = 0, @iCountOfWord = 0 WHILE(@iPosOfMess < @iLenOfMess) BEGIN SELECT @iCountOfWord = @iPosOfMess / 4, @iPosOfWord = @iPosOfMess % 4 UPDATE @tWordArray SET [Word] = [Word] | dbo.MD5_LShift(UNICODE(SUBSTRING(@sOrigMess,@iPosOfMess+1,1)),@iPosOfWord*8) WHERE [ID] = @iCountOfWord SET @iPosOfMess = @iPosOfMess + 1 END SELECT @iCountOfWord = @iPosOfMess / 4, @iPosOfWord = @iPosOfMess % 4 UPDATE @tWordArray SET [Word] = [Word] | dbo.MD5_LShift(0x80,@iPosOfWord*8) WHERE [ID] = @iCountOfWord UPDATE @tWordArray SET [Word] = [Word] | dbo.MD5_LShift(@iLenOfMess,3) WHERE [ID] = @iWordArrayLen - 2 UPDATE @tWordArray SET [Word] = [Word] | dbo.MD5_RShift(@iLenOfMess,29) WHERE [ID] = @iWordArrayLen - 1 RETURN END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_WordToHex]') AND xtype IN(N'FN', N'IF', N'TF')) DROP FUNCTION [dbo].[MD5_WordToHex] GO /***************************************************************************** * Name: MD5_WordToHex * Description: MD5_WordToHex *****************************************************************************/ CREATE FUNCTION dbo.MD5_WordToHex( @iValueINT ) RETURNS CHAR(8) WITH ENCRYPTION AS BEGIN DECLARE @sResVARCHAR(8) DECLARE @iTmpINT DECLARE @iCount TINYINT SELECT @sRes = '', @iCount = 0 WHILE(@iCount<4) BEGIN SET @iTmp = dbo.MD5_RShift(@iValue,@iCount*8) & 0x000000FF SET @sRes = @sRes + CASE @iTmp / 16 WHEN 0THEN '0' WHEN 1THEN '1' WHEN 2THEN '2' WHEN 3THEN '3' WHEN 4THEN '4' WHEN 5THEN '5' WHEN 6THEN '6' WHEN 7THEN '7' WHEN 8THEN '8' WHEN 9THEN '9' WHEN 10 THEN 'A' WHEN 11 THEN 'B' WHEN 12 THEN 'C' WHEN 13 THEN 'D' WHEN 14 THEN 'E' WHEN 15 THEN 'F' ELSE '' END + CASE @iTmp % 16 WHEN 0 THEN '0' WHEN 1THEN '1' WHEN 2THEN '2' WHEN 3THEN '3' WHEN 4THEN '4' WHEN 5THEN '5' WHEN 6THEN '6' WHEN 7THEN '7' WHEN 8THEN '8' WHEN 9THEN '9' WHEN 10 THEN 'A' WHEN 11 THEN 'B' WHEN 12 THEN 'C' WHEN 13 THEN 'D' WHEN 14 THEN 'E' WHEN 15 THEN 'F' ELSE '' END SET @iCount = @iCount + 1 END RETURN(@sRes) END GO IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5]') AND xtype IN(N'FN', N'IF', N'TF')) DROP FUNCTION [dbo].[MD5] GO /***************************************************************************** * Name: MD5 * Description: MD5 *****************************************************************************/ CREATE FUNCTION dbo.MD5( @sOrigMessNVARCHAR(4000) ) RETURNS CHAR(32) WITH ENCRYPTION AS BEGIN --==================================== DECLARE @S11 TINYINT DECLARE @S12 TINYINT DECLARE @S13 TINYINT DECLARE @S14 TINYINT DECLARE @S21 TINYINT DECLARE @S22 TINYINT DECLARE @S23 TINYINT DECLARE @S24 TINYINT DECLARE @S31 TINYINT DECLARE @S32 TINYINT DECLARE @S33 TINYINT DECLARE @S34 TINYINT DECLARE @S41 TINYINT DECLARE @S42 TINYINT DECLARE @S43 TINYINT DECLARE @S44 TINYINT SELECT @S11 = 7, @S12 = 12, @S13 = 17, @S14 = 22 SELECT @S21 = 5, @S22 = 9, @S23 = 14, @S24 = 20 SELECT @S31 = 4, @S32 = 11, @S33 = 16, @S34 = 23 SELECT @S41 = 6, @S42 = 10, @S43 = 15, @S44 = 21 --==================================== DECLARE @a INT DECLARE @b INT DECLARE @c INT DECLARE @d INT DECLARE @AAINT DECLARE @BBINT DECLARE @CCINT DECLARE @DDINT SELECT @a = 0x67452301 ,@b = 0xEFCDAB89 ,@c = 0x98BADCFE ,@d = 0x10325476 --==================================== DECLARE @sResVARCHAR(32) SET @sRes = '' DECLARE @iWordArrayLenINT DECLARE @iWordArrayCountINT DECLARE @tTmp TABLE([ID] INT, [Word] INT) INSERT INTO @tTmp SELECT * FROM dbo.MD5_ConvertToWordArray(@sOrigMess) SELECT @iWordArrayCount=0, @iWordArrayLen = COUNT(*) FROM @tTmp WHILE(@iWordArrayCount < @iWordArrayLen) BEGIN SELECT @AA = @a, @BB = @b, @CC = @c, @DD = @d SELECT @a = dbo.MD5_FF(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 0), @S11, 0xD76AA478) SELECT @d = dbo.MD5_FF(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 1), @S12, 0xE8C7B756) SELECT @c = dbo.MD5_FF(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 2), @S13, 0x242070DB) SELECT @b = dbo.MD5_FF(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 3), @S14, 0xC1BDCEEE) SELECT @a = dbo.MD5_FF(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 4), @S11, 0xF57C0FAF) SELECT @d = dbo.MD5_FF(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 5), @S12, 0x4787C62A) SELECT @c = dbo.MD5_FF(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 6), @S13, 0xA8304613) SELECT @b = dbo.MD5_FF(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 7), @S14, 0xFD469501) SELECT @a = dbo.MD5_FF(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 8), @S11, 0x698098D8) SELECT @d = dbo.MD5_FF(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 9), @S12, 0x8B44F7AF) SELECT @c = dbo.MD5_FF(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 10), @S13, 0xFFFF5BB1) SELECT @b = dbo.MD5_FF(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 11), @S14, 0x895CD7BE) SELECT @a = dbo.MD5_FF(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 12), @S11, 0x6B901122) SELECT @d = dbo.MD5_FF(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 13), @S12, 0xFD987193) SELECT @c = dbo.MD5_FF(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 14), @S13, 0xA679438E) SELECT @b = dbo.MD5_FF(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 15), @S14, 0x49B40821) SELECT @a = dbo.MD5_GG(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 1), @S21, 0xF61E2562) SELECT @d = dbo.MD5_GG(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 6), @S22, 0xC040B340) SELECT @c = dbo.MD5_GG(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 11), @S23, 0x265E5A51) SELECT @b = dbo.MD5_GG(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 0), @S24, 0xE9B6C7AA) SELECT @a = dbo.MD5_GG(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 5), @S21, 0xD62F105D) SELECT @d = dbo.MD5_GG(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 10), @S22, 0x2441453) SELECT @c = dbo.MD5_GG(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 15), @S23, 0xD8A1E681) SELECT @b = dbo.MD5_GG(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 4), @S24, 0xE7D3FBC8) SELECT @a = dbo.MD5_GG(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 9), @S21, 0x21E1CDE6) SELECT @d = dbo.MD5_GG(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 14), @S22, 0xC33707D6) SELECT @c = dbo.MD5_GG(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 3), @S23, 0xF4D50D87) SELECT @b = dbo.MD5_GG(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 8), @S24, 0x455A14ED) SELECT @a = dbo.MD5_GG(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 13), @S21, 0xA9E3E905) SELECT @d = dbo.MD5_GG(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 2), @S22, 0xFCEFA3F8) SELECT @c = dbo.MD5_GG(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 7), @S23, 0x676F02D9) SELECT @b = dbo.MD5_GG(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 12), @S24, 0x8D2A4C8A) SELECT @a = dbo.MD5_HH(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 5), @S31, 0xFFFA3942) SELECT @d = dbo.MD5_HH(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 8), @S32, 0x8771F681) SELECT @c = dbo.MD5_HH(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 11), @S33, 0x6D9D6122) SELECT @b = dbo.MD5_HH(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 14), @S34, 0xFDE5380C) SELECT @a = dbo.MD5_HH(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 1), @S31, 0xA4BEEA44) SELECT @d = dbo.MD5_HH(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 4), @S32, 0x4BDECFA9) SELECT @c = dbo.MD5_HH(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 7), @S33, 0xF6BB4B60) SELECT @b = dbo.MD5_HH(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 10), @S34, 0xBEBFBC70) SELECT @a = dbo.MD5_HH(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 13), @S31, 0x289B7EC6) SELECT @d = dbo.MD5_HH(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 0), @S32, 0xEAA127FA) SELECT @c = dbo.MD5_HH(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 3), @S33, 0xD4EF3085) SELECT @b = dbo.MD5_HH(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 6), @S34, 0x4881D05) SELECT @a = dbo.MD5_HH(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 9), @S31, 0xD9D4D039) SELECT @d = dbo.MD5_HH(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 12), @S32, 0xE6DB99E5) SELECT @c = dbo.MD5_HH(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 15), @S33, 0x1FA27CF8) SELECT @b = dbo.MD5_HH(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 2), @S34, 0xC4AC5665) SELECT @a = dbo.MD5_II(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 0), @S41, 0xF4292244) SELECT @d = dbo.MD5_II(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 7), @S42, 0x432AFF97) SELECT @c = dbo.MD5_II(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 14), @S43, 0xAB9423A7) SELECT @b = dbo.MD5_II(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 5), @S44, 0xFC93A039) SELECT @a = dbo.MD5_II(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 12), @S41, 0x655B59C3) SELECT @d = dbo.MD5_II(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 3), @S42, 0x8F0CCC92) SELECT @c = dbo.MD5_II(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 10), @S43, 0xFFEFF47D) SELECT @b = dbo.MD5_II(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 1), @S44, 0x85845DD1) SELECT @a = dbo.MD5_II(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 8), @S41, 0x6FA87E4F) SELECT @d = dbo.MD5_II(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 15), @S42, 0xFE2CE6E0) SELECT @c = dbo.MD5_II(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 6), @S43, 0xA3014314) SELECT @b = dbo.MD5_II(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 13), @S44, 0x4E0811A1) SELECT @a = dbo.MD5_II(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 4), @S41, 0xF7537E82) SELECT @d = dbo.MD5_II(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 11), @S42, 0xBD3AF235) SELECT @c = dbo.MD5_II(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 2), @S43, 0x2AD7D2BB) SELECT @b = dbo.MD5_II(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 9), @S44, 0xEB86D391) SET @a = dbo.MD5_AddUnsigned(@a, @AA) SET @b = dbo.MD5_AddUnsigned(@b, @BB) SET @c = dbo.MD5_AddUnsigned(@c, @CC) SET @d = dbo.MD5_AddUnsigned(@d, @DD) SET @iWordArrayCount = @iWordArrayCount + 16 END SET @sRes = dbo.MD5_WordToHex(@a) + dbo.MD5_WordToHex(@b) + dbo.MD5_WordToHex(@c) + dbo.MD5_WordToHex(@d) SET @sRes = LOWER(@sRes) RETURN(@sRes) END
----调用MD5
exec dbo.MD5(left(@randNumber,6)