在mysql中选择随机行并限制在每个组内 [英] Select random rows in mysql and limit within each group

问题描述

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

I'm having trouble reducing 12 mysql queries into one call. I need to group the results by product_type_id in a random order and then limit each group by 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 个查询.任何帮助表示赞赏.

There are 12 total queries currently for each product_type_id (1,2,3,...12). Any help appreciated.

推荐答案

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

So you need 7 random records for each product type (7 * 12 records). At first sight you're just looking for a union.

(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 子句.

If you need to order the whole resultset then you can use an ORDER BY clause after the whole UNION.

这篇关于在mysql中选择随机行并限制在每个组内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！