SQL随机名称生成器未在同一行中插入名字和姓氏

admin

SQL随机名称生成器未在同一行中插入名字和姓氏

sql

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

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值）。我敢肯定，这是我所忽略的简单事情。

阅读 200

2021-07-01

共1个答案

admin

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

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

2021-07-01