SQL - 从俱乐部名称中选择一个排序，这样相邻的两行就没有相同的俱乐部名称 [英] SQL - select from the club name with an ordering so no two adjacent rows have the same club name

问题描述

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

I've got the following result when I tried to fetch the results from the database with mysql command :

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  |
+----+------+---------------------+

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

but I have many adjacent rows have the same club name

+----+------+---------------------+
| 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) 但结果不准确

and I have tried to use ORDER BY RAND(club_name) but the result not accurate

所需的输出是这样的:

+----+------+---------------------+
| 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 集成?任何帮助将不胜感激.

can mysql do that or should I integrate php with mysql ? Any help would be much appreciated.

推荐答案

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

Assuming that no club has more than half the members, I think you can do this by enumerating the members with the clubs in order. Then alternate from the first half and the second half using modulo arithmetic:

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;

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

The above solution guarantees that clubs are not next to each other, but the clubs are not very random. Here is another approach that is probably more what you want.

我很难解释为什么它可以在没有白板和一些图片的情况下工作.如果你看细节，它可能会有点明显.我们将列举每个俱乐部的每个成员.然后我们将按幻数排序，其中幻数是序列值乘以会员人数除以俱乐部规模.这将均匀分布结果集中的俱乐部.

It is hard for me to explain why it works without a white board and some picture. If you look at the details, it will probably be sort-of obvious. We are going to enumerate each member of each club. Then we are going to sort by a magic number, where the magic number is the sequential value times the number of members divided by the size of the club. This will evenly distribute the clubs in the result set.

在 SQL 中，这看起来像:

In SQL, this looks like:

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);

这篇关于SQL - 从俱乐部名称中选择一个排序，这样相邻的两行就没有相同的俱乐部名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！