SQL随机名称生成器未在同一行中插入名字和姓氏 [英] SQL random name generator not inserting first and last name in the same row

问题描述

我设法创建了一个简单的查询，该查询选择了一个随机的名字和姓氏并将其插入到结果表中.我想创建一些我可以与运行大量数据的各种测试互换的东西.这是代码(为简单起见，我仅包括5个姓氏和名字):

I managed to create a simple query that selects a random first and last name and inserts them into a result table. I wanted to create something I could interchange with the various tests I run where I have to manufacture a lot of data. Here is the code (I only included 5 first and last names each for simplicity purposes):

SELECT 
    FirstName, LastName
FROM
    (SELECT TOP 1 
         FirstName 
     FROM 
         (SELECT 'John' AS FirstName 
          UNION SELECT 'Tim' AS FirstName 
          UNION SELECT 'Laura' AS FirstName
          UNION SELECT 'Jeff' AS FirstName
          UNION SELECT 'Sara' AS FirstName) AS First_Names 
     ORDER BY NEWID()) n1
FULL OUTER JOIN 
    (SELECT TOP 1
         LastName 
     FROM (SELECT 'Johnson' AS LastName 
           UNION SELECT 'Hudson' AS LastName 
           UNION SELECT 'Jackson' AS LastName
           UNION SELECT 'Ranallo' AS LastName
           UNION SELECT 'Curry' AS LastName) AS Last_Names 
     ORDER BY NEWID()) n2 ON [n1].FirstName = [n2].LastName
WHERE 
    n1.FirstName IS NOT NULL OR n2.LastName IS NOT NULL

以下是结果:

FirstName LastName 
NULL      Hudson
John      NULL

我希望结果返回一行，并随机生成一个名字和姓氏，以便每一行都有一个完整的名字(没有NULL值).我敢肯定，这是我所忽略的简单事情.

I want the results to return one row with a first and last name randomly generated so that each row would have a complete name (no NULL values). I'm sure it's something simple I am overlooking.

推荐答案

以下代码将允许您生成一系列随机名称，其中交叉连接解决方​​案一次仅允许一个.不知道是否需要多次执行此操作，但是如果需要:

The following code will allow you to generate a series of random names, where a cross join solution allows only one at a time. Not sure if you need to do this more than once, but if you do:

create table #table (firstname varchar(50), lastname varchar(50))

declare @counter int = 1 
declare @max int = 5 --set number of repetitions here
declare @a varchar(50)
declare @b varchar(50)

while @counter <= @max
begin

SET @a = (SELECT TOP 1 FirstName 
    FROM (SELECT 'John' AS FirstName 
    UNION SELECT 'Tim' AS FirstName 
    UNION SELECT 'Laura' AS FirstName
    UNION SELECT 'Jeff' AS FirstName
    UNION SELECT 'Sara' AS FirstName) AS First_Names ORDER BY NEWID())

SET @b =
    (SELECT TOP 1 LastName 
    FROM (SELECT 'Johnson' AS LastName 
    UNION SELECT 'Hudson' AS LastName 
    UNION SELECT 'Jackson' AS LastName
    UNION SELECT 'Ranallo' AS LastName
    UNION SELECT 'Curry' AS LastName) AS Last_Names ORDER BY NEWID())

    insert into #table values (@a, @b)

    set @counter = @counter + 1
end

select * from #table

这篇关于SQL随机名称生成器未在同一行中插入名字和姓氏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！