MYSQL从每个类别中选择一个随机记录 [英] MYSQL Select One Random record from each Category

问题描述

我有一个带有Items表的数据库，该表看起来像这样:

I have a database with an Items table that looks something like this:

id
name
category (int)

有数十万条记录.每个item可以位于7个不同的categories之一中，这些categories对应于categories表:

There are several hundred thousand records. Each item can be in one of 7 different categories, which correspond to a categories table:

id
category

我想要一个从每个类别中选择1个随机项目的查询.解决这个问题的最佳方法是什么?我知道对相似的随机查询使用和LIMIT 1，但是我从未做过这样的事情.

I want a query that chooses 1 random item, from each category. Whats the best way of approaching that? I know to use Order By rand() and LIMIT 1for similar random queries, but I've never done something like this.

推荐答案

此查询以随机顺序返回所有加入类别的项目:

This query returns all items joined to categories in random order:

SELECT
c.id AS cid, c.category, i.id AS iid, i.name
FROM categories c
INNER JOIN items i ON c.id = i.category
ORDER BY RAND()

要将每个类别限制为一个类别，请将查询包装在 partial GROUP BY中:

To restrict each category to one, wrap the query in a partial GROUP BY:

SELECT * FROM (
    SELECT
    c.id AS cid, c.category, i.id AS iid, i.name
    FROM categories c
    INNER JOIN items i ON c.id = i.category
    ORDER BY RAND()
) AS shuffled_items
GROUP BY cid

请注意，当查询同时具有GROUP BY和ORDER BY子句时，将在排序之前执行分组.这就是为什么我使用了两个查询的原因:第一个对结果进行排序，第二个对结果进行分组.

Note that when a query has both GROUP BY and ORDER BY clause, the grouping is performed before sorting. This is why I have used two queries: the first one sorts the results, the second one groups the results.

我知道此查询不会赢得任何比赛.我愿意接受建议.

I understand that this query isn't going to win any race. I am open to suggestions.

这篇关于MYSQL从每个类别中选择一个随机记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！