Mysql连接两个表并避免重复

Question

我有下表

表 1

id    name  
1    joseph
2    john
3    george
4    lisa
5    michelle

表2

id    city
1    New York
2    Los Angeles
3    Chicago

我需要获取表 1 中的所有数据并从表 2 中分配一个随机城市

这是我的问题

SELECT * FROM `table1` 
LEFT JOIN (Select * From `table2` Order by Rand()) as temp
ON 1 = 1 

问题是输出看起来像这样

joseph    New York 
joseph    Chicago
joseph    Los Angeles
john      New York 
john      Chicago
john      Los Angeles
george    New York 
george    Chicago
george    Los Angeles

等等

Answer 1

我会在选择中使用子查询:

SELECT 
  id,
  name,
  (SELECT city FROM `table2` ORDER BY RAND() LIMIT 1) random_city
FROM `table1`