美文网首页大数据其他
MySQL获取分组后的TOP 1和TOP N记录(转)

MySQL获取分组后的TOP 1和TOP N记录(转)

作者: wuxuan94 | 来源:发表于2017-09-21 17:17 被阅读28次

    有时会碰到一些需求,查询分组后的最大值,最小值所在的整行记录或者分组后的top n行的记录,在一些别的数据库可能有窗口函数可以方面的查出来,但是MySQL没有这些函数,没有直接的方法可以查出来,可通过以下的方法来查询。

    准备工作
    测试表结构如下:

    root:test> show create table test1\G
    *************************** 1. row ***************************
           Table: test1
    Create Table: CREATE TABLE `test1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `course` varchar(20) DEFAULT NULL,
      `score` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    

    插入数据:

    insert into test1(name,course,score)
    values
    ('张三','语文',80),
    ('李四','语文',90),
    ('王五','语文',93),
    ('张三','数学',77),
    ('李四','数学',68),
    ('王五','数学',99),
    ('张三','英语',90),
    ('李四','英语',50),
    ('王五','英语',89);
    

    查看结果:

    root:test>  select * from test1;
    +----+--------+--------+-------+
    | id | name   | course | score |
    +----+--------+--------+-------+
    |  1 | 张三   | 语文   |    80 |
    |  2 | 李四   | 语文   |    90 |
    |  3 | 王五   | 语文   |    93 |
    |  4 | 张三   | 数学   |    77 |
    |  5 | 李四   | 数学   |    68 |
    |  6 | 王五   | 数学   |    99 |
    |  7 | 张三   | 英语   |    90 |
    |  8 | 李四   | 英语   |    50 |
    |  9 | 王五   | 英语   |    89 |
    +----+--------+--------+-------+
    

    TOP 1
    查询每门课程分数最高的学生以及成绩
    1、使用自连接【推荐】

    root:test> select a.name,a.course,a.score from
        -> test1 a
        -> join (select course,max(score) score from test1 group by course) b 
        -> on a.course=b.course and a.score=b.score;
    +--------+--------+-------+
    | name   | course | score |
    +--------+--------+-------+
    | 王五   | 语文   |    93 |
    | 王五   | 数学   |    99 |
    | 张三   | 英语   |    90 |
    +--------+--------+-------+
    3 rows in set (0.00 sec)
    

    2、使用相关子查询

    root:test> select name,course,score from test1 a 
         -> where score=(select max(score) from test1 where a.course=test1.course);
    +--------+--------+-------+
    | name   | course | score |
    +--------+--------+-------+
    | 王五   | 语文   |    93 |
    | 王五   | 数学   |    99 |
    | 张三   | 英语   |    90 |
    +--------+--------+-------+
    3 rows in set (0.00 sec)
    

    或者

    root:test> select name,course,score from test1 a
        -> where not exists(select 1 from test1 where a.course=test1.course and a.score < test1.score);
    +--------+--------+-------+
    | name   | course | score |
    +--------+--------+-------+
    | 王五   | 语文   |    93 |
    | 王五   | 数学   |    99 |
    | 张三   | 英语   |    90 |
    +--------+--------+-------+
    3 rows in set (0.00 sec)
    

    TOP N
    N>=1
    查询每门课程前两名的学生以及成绩
    1、使用union all
    如果结果集比较小,可以用程序查询单个分组结果后拼凑,也可以使用union all

    root:test> (select name,course,score from test1 where course='语文' order by score desc limit 2)
        -> union all
        -> (select name,course,score from test1 where course='数学' order by score desc limit 2)
        -> union all
        -> (select name,course,score from test1 where course='英语' order by score desc limit 2);
    +--------+--------+-------+
    | name   | course | score |
    +--------+--------+-------+
    | 王五   | 语文   |    93 |
    | 李四   | 语文   |    90 |
    | 王五   | 数学   |    99 |
    | 张三   | 数学   |    77 |
    | 张三   | 英语   |    90 |
    | 王五   | 英语   |    89 |
    +--------+--------+-------+
    6 rows in set (0.01 sec)
    

    2、自身左连接

    root:test> select a.name,a.course,a.score
        -> from test1 a left join test1 b on a.course=b.course and a.score<b.score
        -> group by a.name,a.course,a.score
        -> having count(b.id)<2
        -> order by a.course,a.score desc;
    +--------+--------+-------+
    | name   | course | score |
    +--------+--------+-------+
    | 王五   | 数学   |    99 |
    | 张三   | 数学   |    77 |
    | 张三   | 英语   |    90 |
    | 王五   | 英语   |    89 |
    | 王五   | 语文   |    93 |
    | 李四   | 语文   |    90 |
    +--------+--------+-------+
    6 rows in set (0.00 sec)
    

    3、相关子查询

    root:test> select *
        -> from test1 a
        -> where 2>(select count(*) from test1 where course=a.course and score>a.score)
        -> order by a.course,a.score desc;
    +----+--------+--------+-------+
    | id | name   | course | score |
    +----+--------+--------+-------+
    |  6 | 王五   | 数学   |    99 |
    |  4 | 张三   | 数学   |    77 |
    |  7 | 张三   | 英语   |    90 |
    |  9 | 王五   | 英语   |    89 |
    |  3 | 王五   | 语文   |    93 |
    |  2 | 李四   | 语文   |    90 |
    +----+--------+--------+-------+
    6 rows in set (0.01 sec)
    

    4、使用用户变量

    root:test> set @num := 0, @course := '';
    Query OK, 0 rows affected (0.00 sec)
    
    root:test> 
    root:test> select name, course, score
        -> from (
        ->    select name, course, score,
        ->       @num := if(@course = course, @num + 1, 1) as row_number,
        ->       @course := course as dummy
        ->   from test1
        ->   order by course, score desc
        -> ) as x where x.row_number <= 2;
    +--------+--------+-------+
    | name   | course | score |
    +--------+--------+-------+
    | 王五   | 数学   |    99 |
    | 张三   | 数学   |    77 |
    | 张三   | 英语   |    90 |
    | 王五   | 英语   |    89 |
    | 王五   | 语文   |    93 |
    | 李四   | 语文   |    90 |
    +--------+--------+-------+
    6 rows in set (0.00 sec)
    

    转自:http://www.cnblogs.com/prayer21/p/6029694.html

    相关文章

      网友评论

        本文标题:MySQL获取分组后的TOP 1和TOP N记录(转)

        本文链接:https://www.haomeiwen.com/subject/egktextx.html