mysql - SQL - 从俱乐部名称中按顺序进行选择，因此相邻的两个行都不会具有相同的俱乐部名称

Question

当我尝试使用 mysql 命令从数据库中获取结果时，得到以下结果:

select player_id,full_name,club_name from players

类似的东西:

+----+------+---------------------+
| id |   full_name  |  club_name  |
+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   El Ahly   |
+----+------+---------------------+
| 3  | Maher Zein   |   El Ahly   |
+----+------+---------------------+
| 4  | Mohab Saeed  |   El Ahly   |
+----+------+---------------------+
| 5  | Kamal saber  |   wadi dgla |
+----+------+---------------------+
| 6  | gamel kamel  |   el-nasr   |
+----+------+---------------------+
| 7  | saed ali     |   Cocorico  |
+----+------+---------------------+
| 8  | omar galal   |   Cocorico  |
+----+------+---------------------+
| 9  | Kamal saber  |   Cocorico  |
+----+------+---------------------+
| 10 | Kareem Gaber |   Enpy      |
+----+------+---------------------+
| 11 | Mohamed gad  |   Ismaily   |
+----+------+---------------------+
| 12 | ehab zeyad   |   Ismaily   |
+----+------+---------------------+
| 13 | moaz maged   |   Smouha    |
+----+------+---------------------+
| 14 | mazen mahmod |   elmasry   |
+----+------+---------------------+
| 15 | ahmed shawky |   Petroget  |
+----+------+---------------------+
| 16 | shaker ali   |   Petroget  |
+----+------+---------------------+

但我有许多相邻行具有相同的俱乐部名称

+----+------+---------------------+
| id |   full_name  |  club_name  |
+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   El Ahly   |
+----+------+---------------------+
| 3  | Maher Zein   |   El Ahly   |
+----+------+---------------------+
| 4  | Mohab Saeed  |   El Ahly   |
+----+------+---------------------+

或

+----+------+---------------------+
| 7  | saed ali     |   Cocorico  |
+----+------+---------------------+
| 8  | omar galal   |   Cocorico  |
+----+------+---------------------+
| 9  | Kamal saber  |   Cocorico  |
+----+------+---------------------+

或

+----+------+---------------------+
| 11 | Mohamed gad  |   Ismaily   |
+----+------+---------------------+
| 12 | ehab zeyad   |   Ismaily   |
+----+------+---------------------+

或

+----+------+---------------------+
| 15 | ahmed shawky |   Petroget  |
+----+------+---------------------+
| 16 | shaker ali   |   Petroget  |
+----+------+---------------------+

我尝试使用ORDER BY RAND(club_name)但结果不准确

期望的输出是这样的:

+----+------+---------------------+
| id |   full_name  |  club_name  |
+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 5  | Kamal saber  |   wadi dgla |
+----+------+---------------------+
| 6  | gamel kamel  |   el-nasr   |
+----+------+---------------------+
| 7  | saed ali     |   Cocorico  |
+----+------+---------------------+
| 2  | Kareem Gaber |   El Ahly   |
+----+------+---------------------+
| 11 | Mohamed gad  |   Ismaily   |
+----+------+---------------------+
| 8  | omar galal   |   Cocorico  |
+----+------+---------------------+
| 3  | Maher Zein   |   El Ahly   |
+----+------+---------------------+
| 9  | Kamal saber  |   Cocorico  |
+----+------+---------------------+
| 4  | Mohab Saeed  |   El Ahly   |
+----+------+---------------------+
| 11 | Mohamed gad  |   Ismaily   |
+----+------+---------------------+
| 13 | moaz maged   |   Smouha    |
+----+------+---------------------+
| 14 | mazen mahmod |   elmasry   |
+----+------+---------------------+
| 15 | ahmed shawky |   Petroget  |
+----+------+---------------------+
| 10 | Kareem Gaber |   Enpy      |
+----+------+---------------------+
| 16 | shaker ali   |   Petroget  |
+----+------+---------------------+

mysql 可以做到这一点还是我应该将 php 与 mysql 集成？任何帮助将不胜感激。

Answer 1

假设没有一个俱乐部拥有超过一半的成员(member)，我认为你可以通过按顺序枚举俱乐部的成员(member)来做到这一点。然后使用模运算交替使用前半部分和后半部分:

select p.*
from (select p.*, (@rn := @rn + 1) as rn
      from players p cross join
           (select @rn := 0) params
      order by club_name
     ) p
order by rn % floor(@rn / 2), rn;

编辑:

上述解决方案保证俱乐部不会彼此相邻，但俱乐部不是很随机。这是另一种可能更符合您需求的方法。

我很难解释为什么它在没有白板和一些图片的情况下工作。如果你看一下细节，它可能是显而易见的。我们将列举每个俱乐部的每个成员。然后我们将按魔数(Magic Number)进行排序，其中魔数(Magic Number)是顺序值乘以成员(member)数量除以俱乐部规模。这将在结果集中均匀分布俱乐部。

在 SQL 中，这看起来像:

select p.*
from (select p.*,
             (@rn := if(@c = club_name, @rn + 1,
                        if(@c := club_name, 1, 1)
                       )
             ) as rn
      from players p cross join
           (select @rn := 0, @c := '') params
      order by club_name
     ) p join
     (select club_name, count(*) as cnt
      from players p
      group by club_name
     ) pc
     on p.club_name = pc.club_name
order by rn * (@rn / cnt);