mysql - 在mysql中选择随机行并限制在每个组内

Question

我无法将 12 个 mysql 查询减少到一次调用中。我需要按随机顺序按 product_type_id 对结果进行分组，然后将每组限制为 7:

SELECT * FROM products WHERE product_type_id=1 AND deleted='n' ORDER BY RAND() LIMIT 7;
SELECT * FROM products WHERE product_type_id=2 AND deleted='n' ORDER BY RAND() LIMIT 7;
...
SELECT * FROM products WHERE product_type_id=12 AND deleted='n' ORDER BY RAND() LIMIT 7;

目前每个 product_type_id (1,2,3,...12) 共有 12 个查询。任何帮助表示赞赏。

Answer 1

因此每个产品类型需要 7 条随机记录(7 * 12 条记录)。乍一看，您只是在寻找工会。

(SELECT * FROM products WHERE product_type_id=1 AND deleted='n' ORDER BY RAND() LIMIT 7)
UNION ALL
(SELECT * FROM products WHERE product_type_id=2 AND deleted='n' ORDER BY RAND() LIMIT 7)
UNION ALL
...
UNION ALL
(SELECT * FROM products WHERE product_type_id=12 AND deleted='n' ORDER BY RAND() LIMIT 7)

如果您需要对整个结果集进行排序，那么您可以在整个 UNION 之后使用一个 ORDER BY 子句。