如何用第二个表中的随机行连接MySQL中的两个表

Question

我有两个表，第一个表有10行，第二个有50行

示例表1:

--------------------------------------------------------
|    Name                                image
--------------------------------------------------------
jhony                       |      asset/jhony.jpg
luis                        |      asset/luis.jpg 
diego                       |      asset/diego.jpg
carlos                      |      asset/carlos.jpg
ferry                       |      asset/ferry.jpg

…表2

--------------------------------------------------------
|    color                              percent
--------------------------------------------------------
red                          |      35%
red                          |      40%
red                          |      45%
green                        |      80% 
green                        |      90% 
green                        |      95% 
green                        |      75%
yellow                       |      60%
yellow                       |      65%
.....

我想连接这两个表，所以它看起来像这样

---------------------------------------------------------------------
|    Name                  image             color         percent
---------------------------------------------------------------------
jhony       |      asset/jhony.jpg    |   red     |    35%
luis        |      asset/luis.jpg     |   red     |    45%
diego       |      asset/diego.jpg    |   red     |    35%
carlos      |      asset/carlos.jpg   |   yellow  |    70%
ferry       |      asset/ferry.jpg    |   green   |    85%
....

如果表2在join上有重复的行是可以的，但我不希望表1上有重复的名字。知道怎么做这个查询吗?如果能给点建议就更好了。

谢谢你的帮助。

Answer 1

有一种使用随机值和行号进行连接的方法。查询是:

select
t1.name,
t1.image,
t1.randval,
t2.rownum,
t2.color,
t2.percent
from
(
select *,
floor(1+rand()*(select count(*) from table2)) randval
from table1
) t1
join
(
select *,
row_number() over (order by color) rownum
from table2
) t2
on t1.randval = t2.rownum;

结果如下，例如:

+--------+------------------+---------+--------+--------+---------+
| name   | image            | randval | rownum | color  | percent |
+--------+------------------+---------+--------+--------+---------+
| jhony  | asset/jhony.jpg  |       7 |      7 | red    | 45%     |
| luis   | asset/luis.jpg   |       9 |      9 | yellow | 65%     |
| diego  | asset/diego.jpg  |       3 |      3 | green  | 95%     |
| carlos | asset/carlos.jpg |       7 |      7 | red    | 45%     |
| ferry  | asset/ferry.jpg  |       8 |      8 | yellow | 60%     |
+--------+------------------+---------+--------+--------+---------+

如果您需要tale2中没有重复行的结果，查询是:

select
t1.name,
t1.image,
t1.rownum,
t2.rownum,
t2.color,
t2.percent
from
(
select *,
row_number() over (order by name) rownum
from table1
) t1
join
(
select *,
row_number() over (order by rand()) rownum
from table2
) t2
on t1.rownum = t2.rownum;