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
:
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
and ORDER BY
子句时,将在排序之前执行分组。这就是为什么我使用了两个查询的原因:第一个对结果进行排序,第二个对结果进行分组。
我了解此查询不会赢得任何比赛。我愿意提出建议。