SQLSever: how to generate different random numbers for each row in the select statement ?, Sqlseverselect
The random function of the select statement is a bit false, maybe because it is based on time. In the same select statement, random numbers are generated because the time cannot be staggered, how can different rows have different random numbers?
The following example shows how to generate a random date of a month.
-- Create a view with a minimum of 1 and a maximum of 31. if object_id ('view _ rand_int31 ') is not nullbegindrop view view_rand_int31endgocreate view view_rand_int31as select cast (ceiling (rand () * 31) as [r] go -- create date (day) random function if object_id ('dbo. fun_GetRandDay ') is not nullbegindrop function dbo. fun_GetRandDayendgoCREATE FUNCTION dbo. fun_GetRandDay (@ max INT) returns intas begindeclare @ r intselect @ r = [r] from view_rand_int31while @ r >@maxbeginselect @ r = [r] from orders @ r <= @ maxbeginbreak; endendreturn @ rendgo -- Random Number of multiple records under the test select condition -- Insert the test data row declare @ t table (rowNum int identity, [yearMonth] nvarchar (20 )) declare @ I int, @ imax intselect @ I = 1, @ imax = 28 while @ I <= @ imaxbegininsert into @ t ([yearMonth]) select '2014 'set @ I = @ I + 1end -- execute the query select *, cast ('2017-02-' + cast (dbo. fun_GetRandDay (28) as varchar (2) as datetime) as [date], (select cast (ceiling (rand () * 28) as int )) as [r] from @ t