MySQL如何获取表中前100个值的最小值和最大值 - MySQL How to obtain the MIN and MAX of the top 100 values in a table

MySQL How to obtain the MIN and MAX of the top 100 values in a table

我希望能够在我们的数据库中找到前100名分数的最大值和最小值。

看起来应该很简单，但是我遗漏了一些东西。

I've try

SELECT 
       MAX(BEST_GAME_SCORE) as max_bgs , 
       MIN(BEST_GAME_SCORE) as min_bgs 
FROM user 
WHERE BEST_GAME_SCORE IN (SELECT BEST_GAME_SCORE 
                          FROM user 
                          ORDER BY BEST_GAME_SCORE DESC 
                          LIMIT 100)

但是…LIMIT在子查询

中不起作用

我试过使用JOIN，但我总是得到0的最小值，其中最小的连接不应该是0

SELECT 
    MAX(user.BEST_GAME_SCORE), 
    MIN(user.BEST_GAME_SCORE) 
FROM user 
  JOIN ( SELECT user.BEST_GAME_SCORE 
         FROM user 
         ORDER BY user.BEST_GAME_SCORE DESC 
         LIMIT 100) latest

编辑:例如如果我有一个具有以下分数的表

我想选择前5个分数(即1000,900,800,700,600)，但查询只是返回最大值和最小值，所以1000和600。对不起，我以为我说的很清楚。

使用子查询:

SELECT MAX(BEST_GAME_SCORE) as max_bgs , MIN(BEST_GAME_SCORE) as min_bgs
from (SELECT BEST_GAME_SCORE
      FROM user
      ORDER BY BEST_GAME_SCORE DESC
      LIMIT 100
     ) u;