MySQL Query to receive random combinations from tw

问题:

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;

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?

回答1:

Is this what you're looking for?

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

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.

回答2:

Order de final result by rand() as well before you do limit 10, that should shuffle your 500000000 results around to give you what you want.