当我尝试使用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集成在一起？任何帮助将非常感激。
最佳答案
假设没有一个俱乐部拥有超过一半的会员，我想您可以通过按顺序列出俱乐部中的会员来做到这一点。然后使用模运算从上半年和下半年交替进行：
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;
编辑：
上述解决方案保证了球杆不会彼此相邻，但是球杆不是很随机。这是另一种可能更理想的方法。
我很难解释为什么没有白板和一些图片就可以工作。如果您查看详细信息，可能会很明显。我们将列举每个俱乐部的每个成员。然后，我们将根据一个魔幻数字进行排序，其中魔幻数字是顺序值乘以成员数除以俱乐部的大小。这将在结果集中平均分配俱乐部。
在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);
关于mysql - SQL-从俱乐部名称中选择一个有序的名称，这样相邻的两行都不会具有相同的俱乐部名称，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31214722/