mysql - 按最高点和最短时间分组

Question

我想查询最高点的排名，如果点数相同，则查看完成时间最短。我的 table 是

 +-------------------------------------------------+
 | | id | user_id | point | finishtime  | week  | |
 +-------------------------------------------------+
 | | 1  | G1      | 1560  |    55       |    1  | |
 | | 2  | G1      | 1560  |    43       |    1  | |
 | | 3  | G1      | 1530  |    55       |    1  | |
 | | 4  | G2      | 1760  |    45       |    1  | |
 | | 5  | G3      | 1760  |    46       |    1  | |
 | | 6  | G3      | 1330  |    25       |    2  | |
 | | 7  | G4      | 360   |    65       |    1  | |
 | | 8  | G2      | 1760  |    50       |    1  | |

预期结果是

 +-------------------------------------------------+
 | | id | user_id | point | finishtime  | week  | |
 +-------------------------------------------------+
 | | 4  | G2      | 1760  |    45       |    1  | |
 | | 5  | G3      | 1760  |    46       |    1  | |
 | | 2  | G1      | 1560  |    43       |    1  | |
 | | 7  | G4      | 360   |    65       |    1  | |
 | | 6  | G3      | 1330  |    25       |    2  | |

我试过为该点选择最大值，但它不会花费最短的完成时间。

我需要按唯一 user_id 对结果进行分组，按最高点和最短完成时间排序。

我需要使用 if else 语句吗？

Answer 1

这是一个可能的答案:

SELECT t1.* FROM t t1
JOIN (
  SELECT t1.user_id, t1.week, t1.point, min(t1.finishtime) ft FROM t t1
  LEFT JOIN t t2
  ON t1.week = t2.week AND t1.user_id = t2.user_id AND t1.point < t2.point
  WHERE t2.point IS NULL
  GROUP BY t1.user_id, t1.week, t1.point
) t2 ON t1.user_id = t2.user_id AND t1.week = t2.week AND
        t1.point = t2.point AND t1.finishtime = t2.ft
ORDER BY t1.week, t1.point DESC, t1.finishtime

结果:

| ID | USER_ID | POINT | FINISHTIME | WEEK |
|----|---------|-------|------------|------|
|  4 |      G2 |  1760 |         45 |    1 |
|  5 |      G3 |  1760 |         46 |    1 |
|  2 |      G1 |  1560 |         43 |    1 |
|  7 |      G4 |   360 |         65 |    1 |
|  6 |      G3 |  1330 |         25 |    2 |

fiddle here .

这基本上是一个双最大的每组问题，因为您需要首先获得最高分，然后是最短完成时间的。

另一种解决方案是使用双组...但是这将涉及第三个嵌套级别并试图避免它，因此选择了左连接解决方​​案。