mysql - 使用多个选择器从 SQL 中的一组中选择一行

Question

(此问题特定于 MySQL 5.6，不包括 CTE)

假设我有一个这样的表(实际表是由一个具有多个连接的子查询组成的，并且要复杂一些):

ID  NAME    POWER    ACCESS_LEVEL  DEPTH
1   Odin    poetry   1             0
1   Isis    song     2             1
2   Enki    water    1             0
2   Zeus    storms   2             2
2   Thor    hammer   2             3

我想先按 ID 对它们进行分组(如果这很重要，它实际上是按 PRINCIPAL_TYPE、PRINCIPAL_ID 进行双重分组)，然后从每个组中选择一行，首选具有最高 ACCESS_LEVEL 的行，并在具有相同访问权限的行中，选择深度最低的行。没有两行具有相同的 (ID, DEPTH)，因此我们无需担心超出这一点的问题。

以上表为例，我们选择:

ID  NAME    POWER    ACCESS_LEVEL  DEPTH
1   Isis    song     2             1
2   Zeus    storms   2             2

这些团体是(Odin、Isis)和(Enki、Thor、Zeus)。在第一组中，我们更喜欢 Odin 而不是 Isis，因为 Odin 具有更高的 ACCESS_LEVEL。在第二组中，我们选择 Zeus，因为 Zeus 和 Thor 的 ACCESS_LEVEL 比 Enki 高，而在这两者之间，Zeus 的深度较低。

最坏的情况是 Ontario，我可以在应用程序级别执行此操作，但在数据库级别执行此操作允许使用 LIMIT 和 SORT BY 进行分页，而不是获取整个结果集。

Answer 1

这是一种使用相关子查询的方法:

select t.*
from t
where (access_level, depth) = (select access_level, depth
                               from t t2
                               where t2.id = t.id
                               order by access_level desc, depth asc
                               limit 1
                              );