mysql - SQL - 在一个表的同一列中选择与上面不相似的行

Question

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

从球员中选择player_id、full_name、club_name

类似的东西:

+----+------+---------------------+
| id |   full_name  |  club_name  |
+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   El Ahly   |
+----+------+---------------------+
| 3  | Wael Gamal   |   ENPPI     |
+----+------+---------------------+
| 4  | Mohab Saeed  |   Petrojet  |
+----+------+---------------------+
| 5  | Kamal saber  |   Cocorico  |
+----+------+---------------------+
| 6  | Mohamed mezo |   Ismaily   |
+----+------+---------------------+
| 7  | Mohamed gad  |   Ismaily   |
+----+------+---------------------+
| 8  | moaz maged   |   Smouha    |
+----+------+---------------------+

但是我有很多俱乐部名称与上面类似

+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   El Ahly   |
+----+------+---------------------+

或

+----+------+---------------------+
| 6  | Mohamed mezo |   Ismaily   |
+----+------+---------------------+
| 7  | Mohamed gad  |   Ismaily   |
+----+------+---------------------+

我尝试使用ORDER BY RAND(club_name)，它给了我这样的结果

例如:

+----+------+---------------------+
| id |   full_name  |  club_name  |
+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   Petrojet  |
+----+------+---------------------+
| 3  | Wael Gamal   |   ENPPI     |
+----+------+---------------------+
| 4  | Mohab Saeed  |   El Ahly   |
+----+------+---------------------+
| 5  | Kamal saber  |   Cocorico  |
+----+------+---------------------+
| 6  | Mohamed mezo |   Ismaily   |
+----+------+---------------------+
| 7  | Mohamed gad  |   Ismaily   |
+----+------+---------------------+
| 8  | moaz maged   |   Smouha    |
+----+------+---------------------+

期望的输出是:

+----+------+---------------------+
| id |   full_name  |  club_name  |
+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   Petrojet  |
+----+------+---------------------+
| 3  | Wael Gamal   |   ENPPI     |
+----+------+---------------------+
| 4  | Mohab Saeed  |   El Ahly   |
+----+------+---------------------+
| 5  | Kamal saber  |   Cocorico  |
+----+------+---------------------+
| 6  | Mohamed mezo |   Ismaily   |
+----+------+---------------------+
| 7  | Mohamed gad  |   Cocorico  |
+----+------+---------------------+
| 8  | moaz maged   |   Smouha    |
+----+------+---------------------+

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

Answer 1

如果我理解正确的话，您不希望具有相同俱乐部名称的球员相继出现在您的结果集中。

您需要的查询可能如下:

SELECT p2.id, p2.full_name, p2.club_name
FROM (
    SELECT 
        id,
        @row_number := CASE
            WHEN @clubName = club_name THEN @row_number + 1
            ELSE 1
        END AS sort_num,
        full_name,
        @clubName := club_name as club_name
    FROM
        players p1
    ORDER BY p1.club_name
) AS p2 ORDER BY p2.sort_num, p2.id;

让我尝试解释一下。您需要按某些列对行进行排序，该列将包含每个非唯一俱乐部名称的唯一编号。 Oracle DB 中有一个非常好的功能，称为 ROW_NUMBER。不幸的是，MySQL 没有它。所以我们正在模仿它。

子查询

SELECT 
    id,
    @row_number := CASE
        WHEN @clubName = club_name THEN @row_number + 1
        ELSE 1
    END AS sort_num,
    full_name,
    @clubName := club_name as club_name
FROM
    players p1
ORDER BY p1.club_name;

将产生以下结果:

+----+----------+--------------+-----------+
| id | sort_num | full_name    | club_name |
+----+----------+--------------+-----------+
|  5 |        1 | Kamal saber  | Cocorico  |
|  1 |        1 | Ahmed Sayed  | El Ahly   |
|  2 |        2 | Kareem Gaber | El Ahly   |
|  3 |        1 | Wael Gamal   | ENPPI     |
|  6 |        1 | Mohamed mezo | Ismaily   |
|  7 |        2 | Mohamed gad  | Ismaily   |
|  4 |        1 | Mohab Saeed  | Petrojet  |
|  8 |        1 | moaz maged   | Smouha    |
+----+----------+--------------+-----------+

注意 sort_num 列。对于俱乐部名称为“El Ahly”的行，它有两个唯一值 1 和 2。现在我们要做的就是按此列排序。

最终结果:

+----+--------------+-----------+
| id | full_name    | club_name |
+----+--------------+-----------+
|  1 | Ahmed Sayed  | El Ahly   |
|  3 | Wael Gamal   | ENPPI     |
|  4 | Mohab Saeed  | Petrojet  |
|  5 | Kamal saber  | Cocorico  |
|  6 | Mohamed mezo | Ismaily   |
|  8 | moaz maged   | Smouha    |
|  2 | Kareem Gaber | El Ahly   |
|  7 | Mohamed gad  | Ismaily   |
+----+--------------+-----------+