MySQL随机选择 [英] MySQL Select by random

问题描述

我在数据库中有一个针对企业的项目列表:

I have a list of items for businesses in a database:

listings
  `id` `location` `item` `status`

所以在数据库中，我可能会有类似的内容:

So in the database I might have something like:

19  Loc A   Green Beans for $12     active
20  Loc B   Two Gatoraids for $3    deactive
21  Loc A   Ham for $2 per lb       active
22  Loc A   Pepsi 2 for $2      active
23  Loc C   House plants $10    active
24  Loc B   Milk Gallon for $1     active
25  Loc C   Ice cream for $5    active

我不想要列出项目，但每个位置仅一个项目，如果某个位置有多个项目，我希望它具有随机的项目.加号仅显示状态为激活的项目.

No what I want to do is list the items, BUT only one item per location, and if there is more than one item for a location I want it have the item be at random. Plus only display item that the status = active.

现在我的桌子已设置为能够有效执行此操作，还是我应该改走另一条路线?

Now is my table set up to be able to do this efficiently or should I go another route?

推荐答案

答案已完全修改-我设法在MySQL上进行了正确的测试，并意识到在MS Access上测试SQL(无论如何都不是个好主意)给了我完全不同的结果.

This answer is completely revised - I managed to test it properly on MySQL and realised testing the SQL on MS Access (not a bright idea anyway) was giving me completely different results.

我认为您需要使用以下内容:

I think you need to use something like this:

SELECT l.id, s.location, s.item
FROM listing AS l, 
          (SELECT location, item, status
           FROM listing
           WHERE status='active'
           ORDER BY RAND()) AS s
WHERE l.location=s.location
AND l.status = 'active'
GROUP BY location;

这篇关于MySQL随机选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！