如何使用 group by 子句选择随机行? [英] How to select a random row with a group by clause?

问题描述

我有下表

SQLFiddle

我试图做的是选择三个随机图像，但要确保没有两个图像具有相同的对象，我试图做的是做一个 GROUP BY 和一个 ORDER BY rand() 但那失败，因为它总是给我 cat1.jpg、dog1.jpg、box1.jpg(路径以 1 结尾的所有图像，而不是其他图像)

What I'm attempting to do is to select three random images but to make sure that no two images have the same object, what I attempted to do is to do a GROUP BY along with an ORDER BY rand() but that is failing as it is always giving me cat1.jpg, dog1.jpg, box1.jpg (All images whose path ends with 1 and not the others)

小提琴包括我运行的查询以及它是如何不起作用的.

The fiddle includes the query I ran and how it is not working.

推荐答案

你需要的是一个 Random 聚合函数.目前的关系型数据库中通常没有这样的功能.

What you need is a Random aggregate function. Usually there are no such functions in the current RDBMSs.

有人问过类似的问题.

所以基本的想法是将元素打乱，然后分组，然后为每个组选择每个组的第一行.如果我们修改链接上提供的答案之一，我们就会得到这个.

So the basic idea is shuffle the elements, then group by, and then for every group just select the first row for every group. If we modify one of answers provided on the link we get this.

select object_id, name, image_path
from
  (SELECT images.image_path AS image_path, objects.id AS object_id, objects.name
  FROM objects LEFT JOIN images ON images.object_id = objects.id
  ORDER BY RAND()) as z
group by z.object_id, z.name

这篇关于如何使用 group by 子句选择随机行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！