MySQL多重排序而忽略？

Question

如果有人想尝试这个.

http://sqlfiddle.com/#!9/904e18/2/0

在我的数据库里我有这样一个：

Group: A
Name: A-1, Points: 7pts
Name: A-2, Points: 6pts
Name: A-3, Points: 6pts
Name: A-4, Points: 5pts

Group: B
Name: B-1, Points: 1pts
Name: B-2, Points: 5pts
Name: B-3, Points: 4pts
Name: B-4, Points: 6pts

Group: C
Name: C-1, Points: 6pts
Name: C-2, Points: 8pts
Name: C-3, Points: 9pts
Name: C-4, Points: 2pts

我需要首先通过的总体领导者对其进行排序，得到最多的分数。(C-3)

然后，我需要按照第二的总体领导对其进行排序，但是而不是在总领导者的团队中。(A-1)

接下来，我需要按照第三的总体领导进行排序，但不是在总体领导或第二领导的小组中(B-4)。

然后按点对剩下的进行排序。

所以应该是这样的：

C-3

A-1

B-4

^^每一组的领导，按最高至最低排序

其余各点依次为最高至最低，无群体特异性。

Answer 1

通过以下查询，您可以以您想要的方式获得前3名团队：

select * from teams
where points = (select max(points) from teams as t where t.`group` = teams.`group`)
order by points desc

您还可以使用以下查询以所需的方式获取其余的团队：

select *  from teams
where name not in 
  (select name from teams
  where points = (select max(points) from teams as t where t.`group` = teams.`group`))
order by points desc

问题是，如果合并了这两个查询，那么就会丢失它们包含的任何顺序。因此，为了组合这两个结果，您必须在2个结果中添加一个sortKey，并将其用于最终结果的排序，以便保留排序。查询如下：

(select *, 1 as SortKey from teams
where points = (select max(points) from teams as t where t.`group` = teams.`group`))

UNION ALL

(select *, 2 as SortKey from teams
where name not in 
  (select name from teams
  where points = (select max(points) from teams as t where t.`group` = teams.`group`)))

order by SortKey, points desc

您也可以在这个木琴中看到它