MYSQL - 选择4个随机行并将它们转置为插入语句的列

Question

数据方案:

create table level (
  id int auto_increment primary key,
  # ... other data which is not relevant
);

create table session (
  id int auto_increment primary key,
  lvlid1 int not null,
  lvlid2 int not null,
  lvlid3 int not null,
  lvlid4 int not null,
  ts timestamp not null default current_timestamp,
  foreign key(lvlid1) references level(id),
  foreign key(lvlid2) references level(id),
  foreign key(lvlid3) references level(id),
  foreign key(lvlid4) references level(id)
);

如何从级别表中选择 4 个随机不同的 ID 到 session 表的 (lvlid1、lvlid2、lvlid3、lvlid4) 中？更具体地说，我想知道是否可以这样做:

insert into session (lvlid1, lvlid2, lvlid3, lvlid4)
values(TRANSPOSE(select id from level order by rand() limit 4))

Answer 1

您可以使用带有动态行号变量的数据透视查询在单独的列中选择四个随机 ID。

SET @row_number = 0;

INSERT INTO session (lvlid1, lvlid2, lvlid3, lvlid4)
SELECT MAX(CASE WHEN t2.rn = 1 THEN t2.id END),
       MAX(CASE WHEN t2.rn = 2 THEN t2.id END),
       MAX(CASE WHEN t2.rn = 3 THEN t2.id END),
       MAX(CASE WHEN t2.rn = 4 THEN t2.id END)
FROM
(
    SELECT t.id, (@row_number:=@row_number + 1) AS rn
    FROM
    (
        SELECT id FROM lv_kb00_level ORDER BY RAND()
    ) AS t
) AS t2