MySQL 查询每组前几名的数据(2021-04-28)

1. 先看看表的结构和数据：
   

   mysql> select * from group_order_test;
   +------------+-------+
   | group_name | value |
   +------------+-------+
   | a          | 1     |
   | a          | 2     |
   | a          | 3     |
   | a          | 4     |
   | a          | 5     |
   | b          | 11    |
   | b          | 22    |
   | b          | 33    |
   | b          | 44    |
   | b          | 55    |
   | b          | 66    |
   | c          | 111   |
   | c          | 222   |
   | c          | 333   |
   +------------+-------+
   14 rows in set

2. 我们知道怎么查询某个组的第几名数据，比如查 a 组的第 4 名数据如下，注意 limit 后的数字比所需要的数字小 1：
   

3. 我们把上述查询结合到表数据中，这样我们增加了一列第 4 名的数值：
   

   mysql> select 
       group_name,
       value,
       (select value from group_order_test as sub_table where sub_table.group_name = main_table.group_name order by value asc limit 3,1) as order_value
   from group_order_test as main_table;
   +------------+-------+-------------+
   | group_name | value | order_value |
   +------------+-------+-------------+
   | a          | 1     | 4           |
   | a          | 2     | 4           |
   | a          | 3     | 4           |
   | a          | 4     | 4           |
   | a          | 5     | 4           |
   | b          | 11    | 44          |
   | b          | 22    | 44          |
   | b          | 33    | 44          |
   | b          | 44    | 44          |
   | b          | 55    | 44          |
   | b          | 66    | 44          |
   | c          | 111   | NULL        |
   | c          | 222   | NULL        |
   | c          | 333   | NULL        |
   +------------+-------+-------------+
   14 rows in set

4. 这时候要查询前 4 名的数据就好说了，直接比较大小：

   mysql> select group_name, value
   from
   (
       select 
           group_name,
           value,
           (select value from group_order_test as sub_table where sub_table.group_name = main_table.group_name order by value asc limit 3,1) as order_value
       from group_order_test as main_table
   ) as t
   where value <= order_value;
   +------------+-------+
   | group_name | value |
   +------------+-------+
   | a          | 1     |
   | a          | 2     |
   | a          | 3     |
   | a          | 4     |
   | b          | 11    |
   | b          | 22    |
   | b          | 33    |
   | b          | 44    |
   +------------+-------+
   8 rows in set

5. 等等，上面的 c 组呢？原来 NULL 值影响了，这样的话我们还得对少于所要排名的数据做一个处理，我们用相应组的最大值来做补充，如果没有相应排名的数值的话就用最大值来做比较：

   mysql> select *
   from
   (
       select 
           group_name,
           value,
           (select value from group_order_test as sub_table where sub_table.group_name = main_table.group_name order by value asc limit 3,1) as order_value,
           (select max(value) from group_order_test as sub_table where sub_table.group_name = main_table.group_name group by group_name) as max_value
       from group_order_test as main_table
   ) as t
   where value <= ifnull(order_value, max_value);
   +------------+-------+-------------+-----------+
   | group_name | value | order_value | max_value |
   +------------+-------+-------------+-----------+
   | a          | 1     | 4           | 5         |
   | a          | 2     | 4           | 5         |
   | a          | 3     | 4           | 5         |
   | a          | 4     | 4           | 5         |
   | b          | 11    | 44          | 66        |
   | b          | 22    | 44          | 66        |
   | b          | 33    | 44          | 66        |
   | b          | 44    | 44          | 66        |
   | c          | 111   | NULL        | 333       |
   | c          | 222   | NULL        | 333       |
   | c          | 333   | NULL        | 333       |
   +------------+-------+-------------+-----------+
   11 rows in set

6. 这下再做比较就能得到正确的前 4 名的数据了：

7. 如果语句以后某个时候还要用，并且排名不确定，可以把排名做为一个变量来传入，这时候由于 limit 中不能用变量，可以使用预编译语句来实现：

8. 如果经常使用，使用存储过程也是个方便的主意，此处略