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

1. 假设我们要查询 group_order_test 表的每个分组的前 4 名的数据。先看看表的结构和数据：

   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：

   mysql> select * from group_order_test where group_name = 'a' order by value asc limit 3, 1;

   +------------+-------+

   | group_name | value |

   +------------+-------+

   | a          | 4     |

   +------------+-------+

   1 row in set

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 名的数据了：

   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,

           (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 |

   +------------+-------+

   | a          | 1     |

   | a          | 2     |

   | a          | 3     |

   | a          | 4     |

   | b          | 11    |

   | b          | 22    |

   | b          | 33    |

   | b          | 44    |

   | c          | 111   |

   | c          | 222   |

   | c          | 333   |

   +------------+-------+

   11 rows in set

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

   mysql>

   set @order := 3;

    

   set @limit_order := @order - 1;

   prepare stmt from '

               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 ?,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);';

   execute stmt using @limit_order;

   deallocate prepare stmt;

    

   Query OK, 0 rows affected

    

   Query OK, 0 rows affected

    

   Query OK, 0 rows affected

   Statement prepared

    

   +------------+-------+

   | group_name | value |

   +------------+-------+

   | a          | 1     |

   | a          | 2     |

   | a          | 3     |

   | b          | 11    |

   | b          | 22    |

   | b          | 33    |

   | c          | 111   |

   | c          | 222   |

   | c          | 333   |

   +------------+-------+

   9 rows in set

    

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