Mysql - 从分组数据中获取n个结果

Question

我试图按记录获取每组随机 N 个结果，但查询不起作用，

  SELECT `img_url`
   FROM
     (SELECT img_url, 
                  @image_rank := IF(@current_category = category_child, @country_rank + 1, 1) AS image_rank,
                  @current_category := category_child 
       FROM images
       ORDER BY category_child, rand()
     ) ranked
   WHERE image_rank <= 5

当我运行子查询 image_rank 始终为 1 时，每个类别有超过 1000 张图像，有 30 个类别。

Answer 1

在您的帖子中，@country_rank 应为 @image_rank。此外，在CROSS JOIN中添加了所使用的用户变量的初始化。

要获得 rand N 选择，ORDER BY rand() 应位于 image_rank 生成之前。

SELECT category_child, `img_url`, image_rank
FROM
 (SELECT img_url, 
           @image_rank := IF(@current_category = category_child, @image_rank + 1, 1) AS image_rank,
           @current_category := category_child AS category_child
   FROM (SELECT * FROM images ORDER BY category_child, rand()) AS images_rand CROSS JOIN 
      (SELECT @image_rank := 0, @current_category := NULL) param
 ) ranked
WHERE image_rank <= 5;

这是一个演示:

SQL:

-- data
create table images( category_child int, img_url char(20));
insert into images values
(1,'d'),(1,'c'),(1,'a'),(1,'b'),(1,'x'),(1,'s'),
(2,'d'),(2,'c'),(2,'a'),(2,'b'),(2,'x'),(2,'s');
select * from images;

-- Query wanted
SELECT category_child, `img_url`, image_rank
FROM
 (SELECT img_url, 
           @image_rank := IF(@current_category = category_child, @image_rank + 1, 1) AS image_rank,
           @current_category := category_child AS category_child
   FROM (SELECT * FROM images ORDER BY category_child, rand()) AS images_rand CROSS JOIN 
      (SELECT @image_rank := 0, @current_category := NULL) param
 ) ranked
WHERE image_rank <= 5;

输出:

mysql> select * from images;
+----------------+---------+
| category_child | img_url |
+----------------+---------+
|              1 | d       |
|              1 | c       |
|              1 | a       |
|              1 | b       |
|              1 | x       |
|              1 | s       |
|              2 | d       |
|              2 | c       |
|              2 | a       |
|              2 | b       |
|              2 | x       |
|              2 | s       |
+----------------+---------+
12 rows in set (0.00 sec)

mysql> -- Query wanted
mysql> SELECT category_child, `img_url`, image_rank
    -> FROM
    ->  (SELECT img_url,
    ->            @image_rank := IF(@current_category = category_child, @image_rank + 1, 1) AS image_rank,
    ->            @current_category := category_child AS category_child
    ->    FROM (SELECT * FROM images ORDER BY category_child, rand()) AS images_rand CROSS JOIN
    ->       (SELECT @image_rank := 0, @current_category := NULL) param
    ->  ) ranked
    -> WHERE image_rank <= 5;
+----------------+---------+------------+
| category_child | img_url | image_rank |
+----------------+---------+------------+
|              1 | s       |          1 |
|              1 | x       |          2 |
|              1 | b       |          3 |
|              1 | c       |          4 |
|              1 | d       |          5 |
|              2 | b       |          1 |
|              2 | s       |          2 |
|              2 | x       |          3 |
|              2 | c       |          4 |
|              2 | a       |          5 |
+----------------+---------+------------+
10 rows in set (0.00 sec)

-- Run again and generate different rand selection
mysql> SELECT category_child, `img_url`, image_rank
    -> FROM
    ->  (SELECT img_url,
    ->            @image_rank := IF(@current_category = category_child, @image_rank + 1, 1) AS image_rank,
    ->            @current_category := category_child AS category_child
    ->    FROM (SELECT * FROM images ORDER BY category_child, rand()) AS images_rand CROSS JOIN
    ->       (SELECT @image_rank := 0, @current_category := NULL) param
    ->  ) ranked
    -> WHERE image_rank <= 5;
+----------------+---------+------------+
| category_child | img_url | image_rank |
+----------------+---------+------------+
|              1 | x       |          1 |
|              1 | c       |          2 |
|              1 | d       |          3 |
|              1 | a       |          4 |
|              1 | s       |          5 |
|              2 | a       |          1 |
|              2 | c       |          2 |
|              2 | s       |          3 |
|              2 | d       |          4 |
|              2 | x       |          5 |
+----------------+---------+------------+
10 rows in set (0.00 sec)