(
  SELECT 1 AS leader,s.studentID AS leaderID,s.studentID,s.firstname,s.lastname
  From teams AS t
  JOIN students AS s ON s.studentID = t.leaderID
  group by t.leaderID
) UNION (
  SELECT 0 AS leader,t.leaderID,s.lastname
  FROM teams AS t
  JOIN students AS s ON s.studentID = t.studentID
) ORDER BY 2,1 DESC;

该查询由两个SELECT和一个UNION组成.第一个选择仅使用GROUP BY删除重复项的领导者.第二个子查询选择其余学生.最后,结果集按领导者ID按升序排序,按领导者标志按降序排序.

样本输出

leader leaderID studentID firstname lastname
1      123      123       Dave      Jackson
0      123      9         Rick      Rustels
0      123      7776      Blake     Jackson
0      123      123456    Jessie    Roberts
1      233      233       Lilly     Street
0      233      80        Ashly     Kenson
0      233      997       Billy     Street

请注意,您可能需要优化表结构才能运行此查询而不会降低性能.例如,您可能需要为leaderID和studentID列添加索引.

两个查询PHP

通常,一些简单查询比单个复杂查询更快.此外,在某些情况下,在PHP中而不是在sql中实现排序和分组操作时会更快.以下是另一种可能比上述复杂查询运行得更快的方法.制定一些基准测试,以找出哪种更适合您的情况.

<?PHP
$result = [];

// Collect only leaders
$q = 'SELECT s.studentID,s.lastname
    FROM students AS s,teams AS t
    WHERE t.leaderID = s.studentID
    GROUP BY 1';
$cursor = Db::query($q);
while ($row = Db::fetchNext($cursor)) {
    $result[$row['studentID']][] = $row;
}
$cursor->free();

// Collect the rest
$q = 'SELECT s.studentID,teams AS t
    WHERE t.studentID = s.studentID';
$cursor = Db::query($q);
while ($row = Db::fetchNext($cursor)) {
    $leader_id = $row['leaderID'];
    if (isset($result[$leader_id])) {
        $result[$leader_id][] = $row;
    }
}
$cursor->free();

第一个块仅选择领导者并将其存储到$student结果中.

如果存在相应的leaderID,则第二个块收集其余学生并以类似的方式将它们存储到$result中. (您可以通过IN()将引导ID传递给WHERE子句来跳过此检查.)

由于$result最初由领导者填补,领导者将被存储在第一个位置：

Array
(
    [123] => Array
        (
            [0] => Array
                (
                    [studentID] => 123
                    [firstname] => Dave
                    [lastname] => Jackson
                )

            [1] => Array
                (
                    [studentID] => 123456
                    [leaderID] => 123
                    [firstname] => Jessie
                    [lastname] => Roberts
                )
                (skipped...)
        )

    [233] => Array
        (
            [0] => Array
                (
                    [studentID] => 233
                    [firstname] => Lilly
                    [lastname] => Street
                )
                (skipped...)
        )
)

Db :: query()和Db :: fetchNext()是虚函数.前者对数据库执行SQL查询并将游标返回到结果集.后者使用游标获取下一个结果.