mysql中的一组行的随机顺序 [英] Random order for group of rows in mysql

问题描述

也许这很容易，但我不知道如何正确处理这个问题。我有以下表t1与此数据：

  ---------------- -  
 | id | gr_id | 
 ----------------- 
 | 1 | a | 
 | 2 | a | 
 | 3 | b | 
 | 4 | b | 
 | 5 | c | 
 | 6 | c | 
 | 7 | d | 
 | 8 | d | 
 ----------------- 
  

我想随机获得像这样的gr_ids：

  ---------------- -  
 | id | gr_id | 
 ----------------- 
 | 3 | b | 
 | 4 | b | 
 | 5 | c | 
 | 6 | c | 
 | 7 | d | 
 | 8 | d | 
 | 1 | a | 
 | 2 | a | 
 ----------------- 
  

获得有序的gr_ids上升和下降很容易，但获得随机分组的结果，比我想象的要复杂得多。

我不明白，当我使用GROUP BY或某事物类似的，我确信每组只有一行。

谢谢你们将光线带入黑暗中;）

  SELECT x。* 
 FROM my_table x 
 JOIN 
（SELECT DISTINCT gr_id 
，RAND（）rnd 
 FROM my_table 
）y 
 ON y.gr_id = x.gr_id 
 ORDER 
 BY y.rnd 
，x.id; 
  

maybe it´s easy but i have no clue how to handle this correctly. I have the following table t1 with this data:

-----------------
| id    | gr_id |
-----------------
| 1     | a     |
| 2     | a     |
| 3     | b     |
| 4     | b     |
| 5     | c     |
| 6     | c     |
| 7     | d     |
| 8     | d     |
-----------------

I would like to get randomly gr_ids like this:

-----------------
| id    | gr_id |
-----------------
| 3     | b     |
| 4     | b     |
| 5     | c     |
| 6     | c     |
| 7     | d     |
| 8     | d     |
| 1     | a     |
| 2     | a     |
-----------------

Getting ordered gr_ids ascend and descend is pretty easy, but getting randomly grouped results, is pretty more complicated than i thought it is.

I do not get it, when i use GROUP BY or sth. similar i get for sure only one row each group. How can i randomly order groups, what is the trick here???

Thank you guys for bringing light into darkness ;)

解决方案

E.g.:

SELECT x.*
  FROM my_table x
  JOIN 
     ( SELECT DISTINCT gr_id
                     , RAND() rnd 
                  FROM my_table
     ) y
    ON y.gr_id = x.gr_id
 ORDER 
    BY y.rnd
     , x.id;

这篇关于mysql中的一组行的随机顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！