MySQL 查询每组前几名的数据(2021-04-28)
-
假设我们要查询 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|+------------+-------+14rows in set -
我们知道怎么查询某个组的第几名数据，比如查 a 组的第 4 名数据如下，注意 limit 后的数字比所需要的数字小 1：
mysql> select * from group_order_test where group_name ='a'order by value asc limit3,1;+------------+-------+| group_name | value |+------------+-------+| a |4|+------------+-------+1row in set -
我们把上述查询结合到表数据中，这样我们增加了一列第 4 名的数值：
mysql> selectgroup_name,value,(select value from group_order_test as sub_table where sub_table.group_name = main_table.group_name order by value asc limit3,1) as order_valuefrom 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 |+------------+-------+-------------+14rows in set -
这时候要查询前 4 名的数据就好说了，直接比较大小：
mysql> select group_name, valuefrom(selectgroup_name,value,(select value from group_order_test as sub_table where sub_table.group_name = main_table.group_name order by value asc limit3,1) as order_valuefrom group_order_test as main_table) as twhere value <= order_value;+------------+-------+| group_name | value |+------------+-------+| a |1|| a |2|| a |3|| a |4|| b |11|| b |22|| b |33|| b |44|+------------+-------+8rows in set -
等等，上面的 c 组呢？原来 NULL 值影响了，这样的话我们还得对少于所要排名的数据做一个处理，我们用相应组的最大值来做补充，如果没有相应排名的数值的话就用最大值来做比较：
mysql> select *from(selectgroup_name,value,(select value from group_order_test as sub_table where sub_table.group_name = main_table.group_name order by value asc limit3,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_valuefrom group_order_test as main_table) as twhere 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|+------------+-------+-------------+-----------+11rows in set -
这下再做比较就能得到正确的前 4 名的数据了：
mysql> select group_name, valuefrom(selectgroup_name,value,(select value from group_order_test as sub_table where sub_table.group_name = main_table.group_name order by value asc limit3,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_valuefrom group_order_test as main_table) as twhere 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|+------------+-------+11rows in set -
如果语句以后某个时候还要用，并且排名不确定，可以把排名做为一个变量来传入，这时候由于 limit 中不能用变量，可以使用预编译语句来实现：
mysql>set@order:=3;set@limit_order:=@order-1;prepare stmt from 'select group_name, valuefrom(selectgroup_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_valuefrom group_order_test as main_table) as twhere value <= ifnull(order_value, max_value);';execute stmt using@limit_order;deallocate prepare stmt;Query OK,0rows affectedQuery OK,0rows affectedQuery OK,0rows affectedStatement prepared+------------+-------+| group_name | value |+------------+-------+| a |1|| a |2|| a |3|| b |11|| b |22|| b |33|| c |111|| c |222|| c |333|+------------+-------+9rows in setQuery OK,0rows affected - 如果经常使用，使用存储过程也是个方便的主意，此处略