内连接中的mysql随机选择

sdnqo3pr  于 2021-06-17  发布在  Mysql
关注(0)|答案(2)|浏览(348)

问题mysql关于内部连接的随机行查询与我的问题基本相同,但从未得到回答。
我有一个主表m和从表s。s每m包含1到多行。我想一个查询,选择每一个主行连接到一个随机选择的奴隶。
如果表架构是:

M
---
id

S
---
id
mid

然后,在伪代码中,查询将是: select * from m inner join s on m.id = s.mid 哪里 s.id 是从现有值中随机选择的值
这能转换成真正的sql吗?

jgwigjjp

jgwigjjp1#

这可以通过使用 Row_Number() 概念。我们需要在一个分区内随机分配行数值 mid 在table上 s . 然后,从 m 表到 s 使用 mid 以及 row_number = 1 . 每次都会随机选取一行。
在8以下的mysql版本中,我们可以使用用户定义的变量进行仿真 Row_Number() . 要了解这是如何工作的,您可以查看以下答案以获取解释:https://stackoverflow.com/a/53465139/2469308
请注意,与使用子查询(在 SELECT 子句),因为它只会对整个表进行一次排序
db fiddle视图

create table m (id int, m_nm varchar(10));
create table s (id int, 
                mid int references m(mid), 
                s_nm varchar(10));

insert into m values(1, "a");
insert into m values(2, "b");
insert into m values(3, "c");

insert into s values(1, 1, "aa");
insert into s values(2, 1, "aa");
insert into s values(3, 2, "bb");
insert into s values(4, 2, "bbb");
insert into s values(5, 2, "bbbb");
insert into s values(6, 3, "cc");
insert into s values(7, 3, "ccc");

查询

SELECT 
  m.*, s_dt.id, s_dt.mid, s_dt.s_nm 
FROM 
  m 
JOIN 
(
SELECT
  @rn := IF(@m = dt.mid, @rn+1, 1) AS row_num,   
  @m := dt.mid AS mid, 
  dt.id, 
  dt.s_nm 
FROM 
( 
SELECT
   id, mid, s_nm, RAND() as rand_num
 FROM s
 ORDER BY mid, rand_num ) AS dt
CROSS JOIN (SELECT @rn:=0, @m:=0) AS user_vars 
) AS s_dt 
  ON s_dt.mid = m.id AND 
     s_dt.row_num = 1;

结果(运行#1)

| id  | m_nm | id  | mid | s_nm |
| --- | ---- | --- | --- | ---- |
| 1   | a    | 2   | 1   | aa   |
| 2   | b    | 5   | 2   | bbbb |
| 3   | c    | 7   | 3   | ccc  |

结果(运行#2)

| id  | m_nm | id  | mid | s_nm |
| --- | ---- | --- | --- | ---- |
| 1   | a    | 1   | 1   | aa   |
| 2   | b    | 4   | 2   | bbb  |
| 3   | c    | 6   | 3   | cc   |

结果(运行3)

| id  | m_nm | id  | mid | s_nm |
| --- | ---- | --- | --- | ---- |
| 1   | a    | 1   | 1   | aa   |
| 2   | b    | 3   | 2   | bb   |
| 3   | c    | 7   | 3   | ccc  |

mysql 8.0.2+/mariadb 10.3+解决方案简单如下:

SELECT 
  m.*, s_dt.id, s_dt.mid, s_dt.s_nm 
FROM 
  m 
JOIN 
(
  SELECT
    s.*, 
    ROW_NUMBER() OVER w AS row_num
  FROM s
  WINDOW w AS (PARTITION BY mid 
               ORDER BY RAND())
) AS s_dt 
  ON s_dt.mid = m.id AND 
     s_dt.row_num = 1

db fiddle视图

qij5mzcb

qij5mzcb2#

我认为以下查询完成了所需的工作,但使用了子查询(而不是内部联接):

SELECT *, (SELECT id FROM S WHERE S.mid = M.id ORDER BY RAND() LIMIT 1) AS S_id
FROM M

下面是一个测试它的链接。希望有帮助。

相关问题