如何用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结尾，而不是其他的）

小提琴包含我跑过的查询以及它如何不起作用。 >

你需要的是一个随机聚合函数。通常在当前RDBMS中没有这样的函数。

类似的问题有已被问及。

所以基本思想是洗牌元素，然后为每个组选择每个组的第一行。

  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 images ON images.object_id = objects.id 
 ORDER BY RAND（））as z 
 group by z.object_id，z.name 
  

I have the following table

SQLFiddle

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.

解决方案

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

Similar question has been asked.

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屋！