主页 / dba / 问题 / 119068
Accepted
H. Pauwelyn
Asked: 2015-10-25 08:13:17 +0800 CST

为每一行和每一列制作随机数

  • 772

我将在我的数据库中编辑一些虚假数据。但是，如果我将为每一行和每一列制作随机数，它就不会像我想要的那样随机。结果可以在下图中看到。

没有随机数

这是我使用的代码：

WITH x AS 
(
    SELECT mintwee, mineen, nul, pluseen, plustwee 
    FROM Topic
)
SELECT mintwee = CAST(RAND()*100 AS INT),
       mineen = CAST(RAND()*100 AS INT), 
       nul = CAST(RAND()*100 AS INT),
       pluseen = CAST(RAND()*100 AS INT),
       plustwee = CAST(RAND()*100 AS INT)
FROM x

和这个：

WITH x AS 
(
    SELECT 
        mintwee = CAST(RAND()*100 AS INT), 
        mineen = CAST(RAND()*100 AS INT),
        nul = CAST(RAND()*100 AS INT), 
        pluseen = CAST(RAND()*100 AS INT), 
        plustwee = CAST(RAND()*100 AS INT)
    FROM Topic
)
SELECT mintwee, mineen, nul, pluseen, plustwee
FROM x ;

但两者的结果相同。我知道，我必须使用一个UPDATE语句，但这只是在结果好的情况下尝试。之后，我将使用该UPDATE语句。

我使用带有 express 2014 版本的 SQL Server。

sql-server random

2 个回答

  1. Best Answer

    由于表行和 RAND 函数之间没有关联，因此该表达式在查询中只计算一次。

    生成随机值的一种方法是使用 NEWID() 函数。下面的示例生成 0-99 范围之间的随机整数值。模值可以针对不同的范围进行调整，您可以使用此基本技术为日期、字符等生成随机值。

    WITH x AS 
(
  SELECT mintwee, mineen, nul, pluseen, plustwee 
  FROM Topic
)
SELECT mintwee = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       mineen = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       nul = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       pluseen = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       plustwee= CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100
FROM x;

    编辑：

    根据 Aaron 的评论，如果您只需要 32 位整数，则可以简化表达式。其他示例：

    --postive int
SELECT ABS(CHECKSUM(NEWID()));
--postive and negative int
SELECT CHECKSUM(NEWID());
--postive bigint
SELECT CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 7) AS bigint);
--negative and postive bigint
SELECT CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 8) AS bigint);
--dates between now and 1 year ago
SELECT DATEADD(day, -ABS(CHECKSUM(NEWID())) % 365, GETDATE());
--random uppercase character
SELECT CAST(CAST(65 + ABS(CHECKSUM(NEWID()) % 26) AS binary(1)) AS char(1));
    • 6

  2. 尝试这个：

    select Rand1.n as RandomIntegerBetweenAB
     , Rand2.n as RandomIntegerBetweenXY
     , DateAdd(Day, Rand2.n, GetDate()) as RandomDateAddBetweenXY
     , Rand3.n as RandomInteger
from api.RandomIntBetween(0, 100) as Rand1
cross apply api.RandomIntBetween(-365, 365) as Rand2
cross apply api.RandomIntBetween(default, default) as Rand3;

    我们使用这个视图来生成一个随机整数（只是因为 SQL Server 不会简单地让我们Crypt_Gen_Random()在 UDF 中使用）：

    create view api.vw_RandomInt
with schemabinding
as (
    select Cast(Crypt_Gen_Random(4) as int) as n
);

    使用这个视图作为基础，我们可以得到任何随机整数，然后做一些数学来重新调整它：

    create function api.RandomIntBetween (
    @x int = -2147483648
  , @y int = 2147483647
)
returns table
with schemabinding as
return (
    select Cast(Round(c.f, 0, 0) as int) as n
    from (values(
        case when @x < @y then @x else @y end
      , case when @y > @x then @y else @x end
    )) a ([Min], [Max])
    cross join api.vw_RandomInt as b
    cross apply api.NormalizeBetween(
        b.n
      , -2147483648
      , 2147483647
      , a.[Min] - 0.5
      , a.[Max] + 0.5
    ) as c
);

    NormalizeBetween 的定义是：

    create function api.NormalizeBetween (
    @f float = 1.
  , @x float = 0.
  , @y float = 1.
  , @a float = 0.
  , @b float = 1.
)
returns table
with schemabinding as
return (
    select ((@f - @x) * (@b - @a)) / NullIf(@y - @x, 0) + @a as f
);
    • 1

相关问题