mysql获取每个分组的前三名 mysql每组前三名查询

转载

数据挖掘者 2023-06-21 17:21:21

文章标签 mysql获取每个分组的前三名 MySQL mysql 数据表数据 文章分类 MySQL 数据库

假设我们要查询 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

我们知道怎么查询某个组的第几名数据，比如查 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 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

这下再做比较就能得到正确的前 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

如果语句以后某个时候还要用，并且排名不确定，可以把排名做为一个变量来传入，这时候由于 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