sql - 如何设置随机数

Question

我只想知道如何设置随机数，使其介于 1-10 之间并且不显示像 0.744545454 这样的小数？另外，我如何通过执行遍历每条记录，以便每一行都包含自己的“PlayerWeighting”随机数？

下面是我的存储过程:

CREATE PROCEDURE [dbo].[Player_CreateWholeTeam]
    @TeamID INT

AS
SET NOCOUNT ON
BEGIN
DECLARE @FirstName VARCHAR(25)
        DECLARE @Surname VARCHAR(25)
        DECLARE @PlayerWeighting TINYINT

        SELECT @FirstName = TeamName  
        FROM dbo.Team 
        WHERE TeamID = @TeamID

        SELECT @Surname = 'Player' + cast(NumberID AS VARCHAR (10)) 
        FROM dbo.Team CROSS JOIN dbo.Number 
        WHERE TeamID = @TeamID 
        ORDER BY TeamID, NumberId

        SELECT @PlayerWeighting = RAND(1-10)
        FROM dbo.Player

    INSERT INTO dbo.Player (FirstName, Surname, PlayerWeighting, TeamID)
    VALUES (@FirstName, @Surname, @PlayerWeighting, @TeamID)


END

Answer 1

下面是生成随机数的简单代码:

DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random

如果你想做多行，你可以把它放到一个函数中，然后将值设置为该函数的结果。

如果您想在用户定义的函数中使用，您必须先创建一个 View ，因为您不能将 RAND() 函数用作 UDF。

这是一个如何执行该过程的示例:

-- View to be used in UDF
CREATE VIEW Get_RAND
AS
SELECT RAND() AS myRand

GO

--Simple UDF to get a random number
CREATE FUNCTION myRandom(@lower INT, @upper INT ) 
    RETURNS int
as

BEGIN

DECLARE @Random INT;

---- This will create a random number between @lower and @upper   
DECLARE @RAND DECIMAL(10,10) = (SELECT myRand FROM Get_RAND)
set @Random = ROUND(((@Upper - @Lower -1) * @RAND + @Lower), 0)
RETURN @Random

END

-- Use of the function
SELECT dbo.myRandom(1, 100)