mysql查询从3个类别中获取随机项目

Question

我有一个 mysql 表，其中包含 3 种类型的数据(文本、图像、视频)$type 列保存数据的类型。 (类型 = 1 表示文本，2 表示图像，3 表示视频)

我的要求是需要从表中获取20条记录的随机数据，这样它应该有8个文本，8个图像和4个视频。

另外，前 2 个记录的结果应该是图像，接下来 1 个视频和接下来 2 个文本，然后再次重复。

Answer 1

这是一个 MariaDB 10.1 查询，可以满足您的需求。它首先随机选择8个文本、4个视频和8个图像。然后，它为每种类型的数据分配行号，并使用这些行号将值分组在一起(2 个文本、1 个视频、2 个图像)和 ELT功能在组内按图像、视频、文本顺序排序。

SELECT type, value
FROM (
SELECT value, @rn := IF(type = @type, @rn + 1, 1) AS row, @type := type AS type
FROM (
(SELECT type, value
 FROM data
 WHERE type = 1
 ORDER BY RAND()
 LIMIT 8)
UNION ALL
(SELECT type, value
 FROM data
 WHERE type = 2
 ORDER BY RAND()
 LIMIT 8)
UNION ALL
(SELECT type, value 
 FROM data
 WHERE type = 3
 ORDER BY RAND()
 LIMIT 4)
ORDER BY ELT(type, 3, 1, 2)
) d
CROSS JOIN (SELECT @rn := 0, @type := '') r) v
ORDER BY CASE WHEN type = 1 OR type = 2 THEN (row - 1) DIV 2
              ELSE row - 1 END,
         ELT(type, 3, 1, 2)

我做了一个demo on dbfiddle这表明它正在运行。

如果您升级到 MariaDB 10.2，您可以省去这些变量并使用 ROW_NUMBER :

WITH cte AS 
((SELECT type, value, ROW_NUMBER() OVER (PARTITION BY type ORDER BY RAND()) AS row
 FROM data
 WHERE type = 1
 LIMIT 8)
UNION ALL
(SELECT type, value, ROW_NUMBER() OVER (PARTITION BY type ORDER BY RAND()) AS row
 FROM data
 WHERE type = 2
 LIMIT 8)
UNION ALL
(SELECT type, value, ROW_NUMBER() OVER (PARTITION BY type ORDER BY RAND()) AS row 
 FROM data
 WHERE type = 3
 LIMIT 4)
)
SELECT type, value
FROM cte
ORDER BY CASE WHEN type = 1 OR type = 2 THEN (row - 1) DIV 2
              ELSE row - 1 END,
         ELT(type, 3, 1, 2)

此查询也在我的 demo 中进行了演示.