SQL随机数不起作用 [英] SQL Random number not working

问题描述

declare @fieldForceCounter as int
declare @SaleDate as dateTime
declare @RandomNoSeed as decimal

set @fieldForceCounter = 1
set @SaleDate = '1 Jan 2009'
set @RandomNoSeed = 0.0

WHILE @fieldForceCounter <= 3
   BEGIN

    while @SaleDate <= '1 Dec 2009' 
    begin       

        INSERT INTO MonthlySales(FFCode, SaleDate, SaleValue) VALUES(@fieldForceCounter, @SaleDate, RAND(@RandomNoSeed))

        set @saleDate = @saleDate + 1
        set @RandomNoSeed = Rand(@RandomNoSeed) + 1

    end

    set @SaleDate = '1 Jan 2009'
    set @fieldForceCounter = @fieldForceCounter + 1
   END
GO

这个 T-SQL 命令应该在MonthlySales"表的SaleValue"列中插入随机值.

This T-SQL command was supposed to insert random values in the 'SaleValue'-column in the 'MonthlySales'-table.

但它每次都插入'1'.

But it is inserting '1' every time .

可能是什么问题?

推荐答案

两个问题:

• 首先，rand() 函数返回一个 0 到 1 之间的数字.
• 其次，当在同一个查询中多次调用 rand() 时(例如对于更新语句中的多行)，它通常返回相同的数字(我怀疑你上面的算法正在尝试解决，通过将其拆分为多个调用)

• Firstly, the rand() function returns a number between 0 and 1.
• Secondly, when rand() is called multiple times in the same query (e.g. for multiple rows in an update statement), it usually returns the same number (which I suspect your algorithm above is trying to solve, by splitting it into multiple calls)

我最喜欢的解决第二个问题的方法是使用一个保证每次都返回唯一值的函数，比如 newid()，将其转换为 varbinary，并将其用作种子 :)

My favourite way around the second problem is to use a function that's guaranteed to return a unique value each time, like newid(), convert it to varbinary, and use it as the seed :)

经过一些测试，您似乎需要尝试为 @RandomNoSeed 使用不同的数据类型；float 的行为与十进制有些不同，但仍接近固定值，因此我建议完全避免使用 @RandomNoSeed，只需使用:

after some testing, it seems you'll need to try using a different datatype for @RandomNoSeed; float behaves somewhat different to decimal, but still approaches a fixed value, so I'd recommend avoiding the use of @RandomNoSeed altogether, and simply use:

INSERT INTO MonthlySales(FFCode, SaleDate, SaleValue) 
VALUES(@fieldForceCounter, @SaleDate, RAND(convert(varbinary,newid())))

这篇关于SQL随机数不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！