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
我们知道怎么查询某个组的第几名数据，比如查 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
我们把上述查询结合到表数据中，这样我们增加了一列第 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 名的数据就好说了，直接比较大小：
点击(此处)折叠或打开
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
等等，上面的 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
这下再做比较就能得到正确的前 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
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
