mysql - 使用 JOIN，我如何获得该表每个级别的最大 numOfStars？

Question

这是一些数据表

+-----+-------+--------+-----------+------------+-------+------------+---------------------+-------------------+
| id  | level | userId | numOfSecs | numOfStars | score | mostRecent | creationTime        | allLevelsFinished |
+-----+-------+--------+-----------+------------+-------+------------+---------------------+-------------------+
| 113 |     1 | 21     |        12 |          3 |  1000 |          1 | 2017-11-14 17:39:06 |                 0 |
| 114 |    10 | 21     |        25 |          3 |  1000 |          1 | 2017-11-14 17:41:21 |                 0 |
| 115 |     5 | 21     |        16 |          3 |  1000 |          1 | 2017-11-14 18:37:40 |                 0 |
| 119 |     1 | 23     |       194 |          1 |   200 |          0 | 2017-11-15 13:21:50 |                 0 |
| 120 |     1 | 23     |       121 |          1 |   444 |          0 | 2017-11-15 13:16:01 |                 0 |
| 121 |     1 | 23     |       221 |          3 |   333 |          0 | 2017-11-15 18:14:48 |                 0 |
| 122 |     1 | 23     |       343 |          2 |   555 |          0 | 2017-11-15 13:19:54 |                 0 |
| 123 |     1 | 23     |       355 |          1 |   555 |          1 | 2017-11-15 13:21:19 |                 0 |
| 124 |     2 | 23     |       333 |          1 |   333 |          0 | 2017-11-15 15:25:59 |                 0 |
| 125 |     2 | 23     |       444 |          2 |   444 |          0 | 2017-11-15 15:26:26 |                 0 |
| 126 |     2 | 23     |       222 |          2 |  2222 |          0 | 2017-11-15 15:26:59 |                 0 |
| 127 |     2 | 23     |        44 |          1 |   444 |          1 | 2017-11-15 15:27:24 |                 0 |
+-----+-------+--------+-----------+------------+-------+------------+---------------------+-------------------+

我知道我可以使用

SELECT id, userId, numOfSecs, level, max(numOfStars) 
WHERE userId=23 
GROUP BY level 

但是，我有兴趣更熟悉 JOIN，即使 JOIN 通常用于 2 个或更多表。我想对我的查询做的是选择 userId=23 的所有行，然后对第一个查询的结果进行第二次查询，以找到列出的每个级别的最大星数。根据我所做的研究，左内连接可以完成这项工作，但我在使用单个表的别名时遇到困难。

Answer 1

您可以使用 left join 对同一个表进行相同的操作，但在 on 子句中进行一些调整以选择最高的 numOfStars 行

select a.*
from demo a
left join demo b
       on a.userId = b.userId
       and a.level = b.level
       and case when a.numOfStars = b.numOfStars
                then a.score < b.score
                else a.numOfStars < b.numOfStars
                end
where b.id is null
and a.userId = 23

Demo

Note i have added a case clause for a scenario if user id, level and numOfStars are equal then it will pick the row on basis of highest score