MySQL查询从两个表中接收随机组合 [英] MySQL Query to receive random combinations from two tables

问题描述

好的，这是我的问题，我有两个表，一个表名为firstnames，另一个表为lastnames.我在这里尝试做的是从这些名称中找到100个可能的组合作为测试数据.名字表在单列中有5494项，名字表在单列中有88799项.我唯一能提出的查询结果是:

Alright, here is my issue, I have two tables, one named firstnames and the other named lastnames. What I am trying to do here is to find 100 of the possible combinations from these names for test data. The firstnames table has 5494 entries in a single column, and the lastnames table has 88799 entries in a single column. The only query that I have been able to come up with that has some results is:

select * from (select * from firstnames order by rand()) f LEFT JOIN (select * from lastnames order by rand()) l on 1=1 limit 10;

此代码的问题在于，它选择了1个名字，并给出了可以使用的每个名字.尽管这是合理的，但我必须将限制设置为500000000，以便在没有20个名字的情况下获得所有可能的组合(并且我宁愿不杀死我的服务器).但是，我只需要随机生成100个测试数据条目，就无法使用此代码来获得它.谁能给我任何建议吗?

The problem with this code is that it selects 1 firstname and gives every lastname that could go with it. While this is plausible, I will have to set the limit to 500000000 in order to get all the combinations possible without having only 20 first names(and I'd rather not kill my server). However, I only need 100 random generations of entries for test data, and I will not be able to get that with this code. Can anyone please give me any advice?

推荐答案

这是您要寻找的吗?

SELECT * 
FROM (

SELECT firstname
FROM firstnames
ORDER BY RAND( ) 
LIMIT 10
) AS tb1
JOIN (

SELECT lastname
FROM lastnames
ORDER BY RAND( ) 
LIMIT 10
) AS tb2 ON 1=1

这将为您提供10个随机名字和10个随机姓氏的所有组合.更改限制以更改要合并的名称.

This will give you all combinations of 10 random first names and 10 random last names. Change the limits to change how many names you want to combine.

这篇关于MySQL查询从两个表中接收随机组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！