mysql - MySQL 中的 block 式和行式混洗

Question

有没有办法在 mySQL 中按 block 打乱结果集的行？

为了说明:所需的查询将以四种可能的顺序之一随机返回以下四行(但不是其他可能的顺序，例如，第 4 项永远不应在第 3 项之前)。

原始顺序，A在B之前，item1在item2之前

item,   b_order,    i_order
item1,  A,          1
item2,  A,          1
item3,  B,          1
item4,  B,          2

A在B之前，item2在item1之前

item2,  A,          1
item1,  A,          1
item3,  B,          1
item4,  B,          2

B在A之前，item2在item1之前

item3,  B,          1
item4,  B,          2
item2,  A,          1
item1,  A,          1

B在A之前，item1和item2没有交换

item3,  B,          1
item4,  B,          2
item1,  A,          1
item2,  A,          1

我想可能有一种比较优雅的方法来解决这个问题，方法是使用

 SELECT * FROM items ORDER BY random_number_for_each_block, i_order, RAND()

我还没有想出如何生成这个“random_number_for_each_block”变量。

Answer 1

假设我们有

CREATE TABLE `origin` (
  `item` varchar(10) DEFAULT NULL,
  `b_order` varchar(4) DEFAULT NULL,
  `i_order` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

你可以这样做:

SELECT 
  *, 
  @b_rand  := IF(b_order!=@current, FLOOR(1000*RAND()), @b_rand) AS factor,
  @current := b_order AS current, 
  @b_rand + RAND() AS ordinal
FROM
  (SELECT *
   FROM origin
   ORDER BY b_order) AS ordered
CROSS JOIN
  (SELECT @b_rand:=FLOOR(1000*RAND())) AS init
ORDER BY ordinal

如果您已经知道在初始状态下它们按 b_order 分组，则不需要按 block 对项目进行排序的子查询。但拥有它更安全。

检查 fiddle ( block 是分组的，但 block 顺序是随机的； block 内的项目是随机排序的)