时间戳

首页 > 解决方案 > 如何从多列中提取随机行并将它们插入另一个表？

mysql - 如何从多列中提取随机行并将它们插入另一个表？

问题描述

我正在尝试将三个不同表中的行合并到另一个表中。我已经在我的 db-fiddle 表和下面的查询中复制了这个。我的问题是，如果每个表中有 3 行，那么我应该有 27 种可能的组合，但我只得到 3 个。我理解为什么我只得到 3 个，但我不知道如何改变它来做我想做的事想。就像现在一样，如果选择的随机数是 3，它会从每个表中提取 id = 3。我希望每个表的数字是随机的。

练习表和查询

create table First (id int(10), first varchar(255));
Insert into First (id, first) values (1, 'John'), (2, 'Bill'), (3, 'Chad');
create table Middle (id int(10), middle varchar(255));
Insert into Middle (id, middle) values (1, 'Ethan'), (2, 'Dave'), (3, 'Ron');                                                
create table Last (id int(10), last varchar(255));
Insert into Last (id, last) values (1, 'Smith'), (2, 'Miller'), (3, 'Darnold');
create table Full (id int(10) auto_increment primary key, full varchar(255)); 
insert into Full (id) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);               

Update Full u1
join (select id,
             @i:=Floor(1+ RAND() * 3),
             (select concat(l.last, ', ', f.first, ' ', m.middle) 
              from First as f, Middle as m, Last as l
              where f.id = @i and m.id = @i and l.id = @i) full 
      from Full) u2
on u1.id = u2.id
set u1.full = u2.full;
select * from Full

编辑：我试图避免完全重复。

标签： mysql

解决方案

所以你需要 3 个随机数来改善你的混音

Update Full u1
join (select id,
             @i:=Floor(1+ RAND() * 3),
             @j:=Floor(1+ RAND() * 3),
             @k:=Floor(1+ RAND() * 3),
             (select concat(l.last, ', ', f.first, ' ', m.middle) 
              from First as f, Middle as m, Last as l
              where f.id = @i and m.id = @j and l.id = @k) full 
      from Full) u2
on u1.id = u2.id
set u1.full = u2.full;

并使用适当的 JOIN 机制

Update Full u1
join (select id,
             @i:=Floor(1+ RAND() * 3),
             @j:=Floor(1+ RAND() * 3),
             @k:=Floor(1+ RAND() * 3),
             (select concat(l.last, ', ', f.first, ' ', m.middle) 
              from First as f
                left join Middle m on m.id = @j
                left join Last l on l.id = @k
              where f.id = @i ) full 
      from Full) u2
on u1.id = u2.id
set u1.full = u2.full;

推荐阅读