mysql可以覆盖_mysql-SQL：加入后没有覆盖行的GROUP BY？

我有一张篮球联赛表格,一个桌上足球队以及一个这样的球员表格：

LEAGUES

ID | NAME |

------------------

1 | NBA |

2 | ABA |

TEAMS:

ID | NAME | LEAGUE_ID

------------------------------

20 | BULLS | 1

21 | KNICKS | 2

PLAYERS:

ID | TEAM_ID | FIRST_NAME | LAST_NAME |

---------------------------------------------

1 | 21 | John | Starks |

2 | 21 | Patrick | Ewing |

给定联赛ID,我想从该联赛中所有球队中检索所有球员的姓名和球队ID,所以我这样做：

SELECT t.id AS team_id, p.id AS player_id, p.first_name, p.last_name

FROM teams AS t

JOIN players AS p ON p.team_id = t.id

WHERE t.league_id = 1

返回：

[0] => stdClass Object

(

[team_id] => 21

[player_id] => 1

[first_name] => John

[last_name] => Starks

)

[1] => stdClass Object

(

[team_id] => 21

[player_id] => 2

[first_name] => Patrick

[last_name] => Ewing

)

+ around 500 more objects...

由于我将使用此结果填充包含每个团队的球员列表的每个团队的下拉菜单,因此我想按团队ID对我的结果进行分组,因此创建这些下拉列表的循环只需要遍历每个团队ID即可每次全部500个玩家.

但是当我像这样使用GROUP BY时：

SELECT t.id AS team_id, p.id AS player_id, p.first_name, p.last_name

FROM teams AS t

JOIN players AS p ON p.team_id = t.id

WHERE t.league_id = 1

GROUP BY t.id

这样只会从每个团队中返回一名球员,由于使用了相同的列名,因此会覆盖同一团队中的所有其他球员.

[0] => stdClass Object

(

[team_id] => 21

[player_id] => 2

[first_name] => Patrick

[last_name] => Ewing

)

[1] => stdClass Object

(

[team_id] => 22

[player_id] => 31

[first_name] => Shawn

[last_name] => Kemp

)

etc...

我想返回这样的东西：

[0] => stdClass Object

(

[team_id] => 2

[player_id1] => 1

[first_name1] => John

[last_name1] => Starks

[player_id2] => 2

[first_name2] => Patrick

[last_name2] => Ewing

+10 more players from this team...

)

+25 more teams...

有可能吗？