选择随机行，但一列中没有重复的值 [英] Select random rows but without duplicates of values from one column

问题描述

这是从单个表中进行的简单选择.目的是选择四个随机乘积，从x个类别的每一个类别中选择一个，并具有几个"where"限制.我已经尝试过了:

This is a simple select from a single table. The purpose is to select four random products, one from each of x number of categories, with a couple of 'where' limitations. I've tried this:

  SELECT pName, 
         pID 
    from products 
   WHERE pDisplay=1 
     AND pFeatured=1 
GROUP BY pCategory 
ORDER BY RAND() 
   LIMIT 0,4

这种作品，但是它总是返回任何给定类别的相同产品.我想更改显示的产品，同时仍然只显示任何给定类别的单个产品.

This kind of works, but it always returns the same product from any given category. I want to vary the products shown, while still showing only a single product for any given category.

我也尝试过:

  SELECT DISTINCT(pCategory) 
         pName, 
         pID 
    from products 
   WHERE pDisplay=1 
     AND pFeatured=1 
ORDER BY RAND() 
   LIMIT 0,4

我在想，可能需要两个选择-第一个选择一个随机的4个类别，第二个选择从每个类别中选择一个随机行，但是a.不知道该怎么做，以及b.尽可能使用单个查询.

I'm thinking maybe it needs two selects -the first to get a random 4 categories the second to choose a random row from each of them, but a. am not sure how to do this, and b. would prefer to use a single query if possible.

推荐答案

不太好，但是我认为这是在MySQL中可以做到的最好的事情.

Not very nice, but I think this is the best you can do in MySQL.

SELECT p.pID, p.pName
   FROM (
       SELECT (
           SELECT pID FROM products
           WHERE pCategory=p.pCategory AND pDisplay=1 AND pFeatured=1
           ORDER BY rand() LIMIT 1
           ) AS pID
       FROM products p
       WHERE pDisplay=1 AND pFeatured=1
       GROUP BY pCategory
       ORDER BY rand() LIMIT 4
       ) c
   JOIN products p ON c.pID = p.pID

(karim79的查询可以返回同一类别的多个产品，甚至可能返回具有pDisplay = 0或pFeatured = 0的产品.rexem的查询可以处理pDisplay/pFeatures问题，但也可以返回同一类别的多个产品)

(karim79's query can return multiple products from the same category, possibly even products with pDisplay=0 or pFeatured=0. rexem's query handles the pDisplay/pFeatures issue, but can also return multiple products from the same category)

这篇关于选择随机行，但一列中没有重复的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！