mysql - 如何为一个表选择多个值 "cell"MySQL

Question

对于所有球员，我需要找到球员编号和他们曾经效力过的球队数量的列表。

这是“MATCHES”表:

+---------+--------+----------+-----+------+
| MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       2 |      1 |        6 |   2 |    3 |
|       3 |      1 |        6 |   3 |    0 |
|       4 |      1 |       44 |   3 |    2 |
|       5 |      1 |       83 |   0 |    3 |
|       6 |      1 |        2 |   1 |    3 |
|       7 |      1 |       57 |   3 |    0 |
|       8 |      1 |        8 |   0 |    3 |
|       9 |      2 |       27 |   3 |    2 |
|      10 |      2 |      104 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
|      12 |      2 |      112 |   1 |    3 |
|      13 |      2 |        8 |   0 |    3 |
+---------+--------+----------+-----+------+

我能想到的最好的是:

SELECT DISTINCT playerno, teamno
FROM matches
ORDER BY playerno;

结果是:

+----------+--------+
| playerno | teamno |
+----------+--------+
|        2 |      1 |
|        6 |      1 |
|        8 |      1 |
|        8 |      2 |
|       27 |      2 |
|       44 |      1 |
|       57 |      1 |
|       83 |      1 |
|      104 |      2 |
|      112 |      2 |
+----------+--------+

注意玩家 8 如何在两支球队中打球。我怎样才能让表格只显示玩家 8 的一行和 teamno 的列表 (1 & 2)？

Answer 1

您可以使用 group_concat聚合函数:

SELECT   playerno, GROUP_CONCAT(DISTINCT teamno)
FROM     matches
GROUP BY playerno
ORDER BY playerno;