SQL：在没有重写行的联接后按分组？

Question

我有一个篮球联赛表，一个af队表和一个球员表，像这样：

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

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

Answer 1

在SQL中不能这样做，因为不能以数据集的形式表示结果。您希望返回复杂对象。您可以做的是在代码中处理这个问题，并通过返回一个按team_id排序的数据集来帮助您自己。每当team_id发生变化时，就应该在代码中创建新的对象，并用新的球员列表填充它。

应该是这样的（语法可能不正确）：

返回的结果集：

team_id|player_id|first|last
1|1|f1|l1
1|2|f2|l2
1|3|f3|l3
2|5|f5|l5
2|6|f6|l6

当它在代码中返回时

$lastTeamId=0;
$output=array();
foreach($results as $row){
  if($lastTeamId != $row["team_id"]){
    $lastTeamId = $row["team_id"];
    $output[$lastTeamId] = array();
  }

  $newPlayer = null;
  $newPlayer->id = $row["player_id"];
  $newPlayer->first = $row["first"];
  $newPlayer->last = $row["last"];
  $output[$lastTeamId][] = $newPlayer;
}