您的位置: 首页  >  技术问答  >  生成从 3 到 6 的随机整数值

生成从 3 到 6 的随机整数值

分类: 技术问答 • 2022-05-19 22:59:52
问题描述：

是否可以在 Microsoft SQL Server 中生成从 Min 到 Max 的随机整数值(3-9 示例，15-99 e.t.c)

Is it possible in Microsoft SQL Server generate random int value from Min to Max (3-9 example, 15-99 e.t.c)

我知道，我可以从 0 生成到 Max，但是如何增加 Min 边界?

I know, i can generate from 0 to Max, but how to increase Min border?

此查询生成从 1 到 6 的随机值.需要将其从 3 更改为 6.

This query generate random value from 1 to 6. Need to change it from 3 to 6.

SELECT table_name, 1.0 + floor(6 * RAND(convert(varbinary, newid()))) magic_number 
FROM information_schema.tables

5 秒后添加:

愚蠢的问题，对不起...

Stupid question, sorry...

SELECT table_name, 3.0 + floor(4 * RAND(convert(varbinary, newid()))) magic_number 
FROM information_schema.tables

答

一个有用的编辑器在每条语句之前添加了选择"，但此项的重点是它可以为返回中的每一行生成唯一的键，而不仅仅是一项(为此，我将使用 Rand() 函数).例如:从 tblExample 中选择前 100 个 Rand(),*

A helpful editor added the 'Select' before each statement but the point of this item is that it can generate unique keys for each row in a return, not just one item (For that I would us the Rand() function). For example: Select top 100 Rand(),* from tblExample

将为所有 100 行返回相同的随机值.

Would return the same random value for all 100 rows.

虽然:选择前 100 个 ABS(CHECKSUM(NEWID()) % 10),* from tblexample

While: Select top 100 ABS(CHECKSUM(NEWID()) % 10),* from tblexample

将在返回的每一行上返回 0 到 9 之间的不同随机值.因此，虽然选择使复制和粘贴更容易，但如果需要，您可以将逻辑复制到选择语句中.

Would return a different random value between 0 and 9 on each row in the return. So while the select makes it easier to copy and paste, you can copy the logic into a select statement if that is what is required.

这会生成一个 0-9 之间的随机数

This generates a random number between 0-9

SELECT ABS(CHECKSUM(NEWID()) % 10)

1 到 6

SELECT ABS(CHECKSUM(NEWID()) % 6) + 1

3 到 6

SELECT ABS(CHECKSUM(NEWID()) % 4) + 3

动态(基于 Eilert Hjelmeseths 评论，更新以修复错误(+ 到 -))

Dynamic (Based on Eilert Hjelmeseths Comment, updated to fix bug( + to -))

SELECT ABS(CHECKSUM(NEWID()) % (@max - @min - 1)) + @min

根据评论更新:

  • NEWID 生成随机字符串(对于返回的每一行)
  • CHECKSUM 取字符串的值并创建数字
  • 模数 (%) 除以该数字并返回余数(意味着最大值比您使用的数字小 1)
  • ABS 将负面结果变为正面
  • 然后在结果中加 1 以消除 0 个结果(模拟掷骰子)
  • NEWID generates random string (for each row in return)
  • CHECKSUM takes value of string and creates number
  • modulus (%) divides by that number and returns the remainder (meaning max value is one less than the number you use)
  • ABS changes negative results to positive
  • then add one to the result to eliminate 0 results (to simulate a dice roll)

相关推荐