从MySQL的每个类别中选择2种产品 [英] Select 2 products from each category in MySQL

问题描述

与 MYSQL非常相似表格中每个类别的4条记录，但是没有被接受的答案，那里的一个答案没有多大意义，所以我再次询问.

Pretty similar to MYSQL - select first 4 records for each category in a table but there isn't an accepted answer and the one answer there doesn't make much sense so i'm asking again.

我有一个包含3列的PRODUCTS表:ID，NAME和CATEGORY 我现在想知道的是，是否有可能为每个不同的类别选择2种产品，而无需在PHP循环中进行查询.

I have a PRODUCTS table with 3 columns: ID, NAME and CATEGORY What i would like to know now is if it's at all possible to select 2 products for each distinct category without doing queries in a PHP loop.

所选产品的顺序并不重要，它们也可能是随机的.但重要的是，每个类别最多只能有2种产品.

The order of the selected products is of no importance, they might as well be random. But it's important that i only have max 2 products per category.

一个好的结果集将是

ID  ; NAME   ; CATEGORY
:::::::::::::::::::::::
152 ; APPLE  ; FRUIT
185 ; ORANGE ; FRUIT
145 ; BEEF   ; MEAT
141 ; PORK   ; MEAT
410 ; PEPSI  ; DRINKS
585 ; CARROT ; VEGETABLES
585 ; TOMATO ; VEGETABLES

推荐答案

可以遵循以下方法进行操作:

Something along these lines will work:

SELECT id, name, category 
FROM (
  SELECT *, 
         IF( @prev <> category, 
             @rownum := 1, 
             @rownum := @rownum+1 
         ) AS rank, 
         @prev := category, 
         @rownum  
  FROM (
    SELECT * FROM products 
    ORDER BY category, rand()
  ) random_prodcts
) products_ranked 
WHERE rank <= 2;

它在类别中随机排序，然后将它们拉出，以跟踪每个类别有多少.

It orders them randomly within the categories, then pulls them out tracking how many it's got from each.

不确定如何扩展.

尝试了几千条记录，看起来还可以.

Tried it with a few thousand records and it seems ok.

这篇关于从MySQL的每个类别中选择2种产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！