Generate random names in sql

问题:

I have 5 random names each for male and female. I need to insert random names based on the gender. But how can we insert names in random from a set of 5 names in SQL. Is it possible?

回答1:

select name from table order by newid()

回答2:

Create a table with the names, and an integer ID. Then use RAND() with % 5 to get down to a value between 0 and 4 inclusively. Add 1 if you want a male, and 6 if you want a female. Like so:

Create table RandomNames
(id int,
 name varchar(100),
 gender char(1)
)

insert into RandomNames
(id, name,gender)
select 1,'Bill','M'
union
select 2,'John','M'
union
select 3,'Steve','M'
union
select 4,'Mike','M'
union
select 5,'Phil','M'
union
select 6,'Sarah','F'
union
select 7,'Ann','F'
union
select 8,'Marie','F'
union
select 9,'Liz','F'
union
select 10,'Stephanie','F'

declare @wantedGender char(1)

select @wantedGender = 'M'

select name 
from RandomNames
where id =  (CAST(RAND()*100 as int) % 5) + case when @wantedGender = 'M' then 1 else 6 end 

回答3:

Store the 5 random names for male in one table and the 5 random names for female in another table. Select a random integer between 1 and 5 and cross reference to male or female table using an inner join.

回答4:

Working on this problem currently. Rand() wont work, you'll get the same Random number for all 300 rows. NewID() wont give you a value that is usable. You can however use the following formula for a random value between 1 and 5 on every line. NewID and Checksum given you a random number every time, but that includes negative numbers,then you divide by 4 and take the remainder (0-4) so you then take the absolute value of that number and add 1 to it. Then use that random number to select a value from the tables of names.

ABS(Checksum(NewID()) % 4) + 1

Using it in a command:

Create table randomnames (ID int identity, name)
insert into randomnames (name)
Values ('Tom', 'Dick', 'Harry', 'Jughead', 'Archie'
          ,'Sally','Sue','Peggy', 'Betty', 'Veronica')

update sometablename
set Name = case gender
    when 'M' then (select name from randomnames where ID = ABS(Checksum(NewID()) % 4)) + 1
    when 'F' then (select name from randomnames where ID = ABS(Checksum(NewID()) % 4)) + 6
end

回答5:

Create a function

CREATE FUNCTION [dbo].[getRandomName](@gen varchar(10))
RETURNS @name varchar(50)

AS
BEGIN

   SELECT TOP 1 @name = name FROM table WHERE gender=@gen ORDER BY newid()
END

Then, just pass @gen to the function like this

select dbo.getRandomName('male')

Or, update many rows like this:

UPDATE myNewTable 
   SET newName=dbo.getRandomName('male')
WHERE gender='male'

回答6:

In SQL Server, the best way to get "random" is to use newid(). You can sort by this to get a sorted list.

If you have five names for each gender, you can use a CTE to store them. The insert would then look like:

with names as (
      select 'M' as gender, 'Alexander' as name union all
      select 'M', 'Burt' union all
      select 'M', 'Christopher' union all
      select 'M', 'Daniel' union all
      select 'M', 'Eric' union all
      select 'F', 'Alexandra' union all
      select 'F', 'Bertha' union all
      select 'F', 'Christine' union all
      select 'F', 'Daniela' union all
      select 'F', 'Erica'
)
insert into table(name)
    select top 1 name
    from names
    where gender = @MyGender
    order by newid();

回答7:

select top 1 name from RandomNames 
where gender = 'M' order by newid()

Sample fiddle