生成从 3 到 6 的随机整数值 [英] Generate random int value from 3 to 6
问题描述
是否可以在 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)
这篇关于生成从 3 到 6 的随机整数值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!