Mysql group分组取数据方法

2019独角兽企业重金招聘Python工程师标准>>>

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



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 |
+--------+--------+-------+
rows in set (0.00 sec)



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 |
+--------+--------+-------+
rows in set (0.00 sec)


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 |
+--------+--------+-------+
rows in set (0.01 sec)


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 |
+--------+--------+-------+
rows in set (0.00 sec)


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 |
+----+--------+--------+-------+
rows in set (0.01 sec)


root:test> set [@num](https://my.oschina.net/u/1587116) := 0, [@course](https://my.oschina.net/u/3683396) := '';
Query OK, 0 rows affected (0.00 sec)

root:test> 
root:test> select name, course, score
    -> from (
    ->    select name, course, score,
    ->       [@num](https://my.oschina.net/u/1587116) := if([@course](https://my.oschina.net/u/3683396) = course, [@num](https://my.oschina.net/u/1587116) + 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 |
+--------+--------+-------+
rows in set (0.00 sec)

转载于:https://my.oschina.net/jsycwangwei/blog/2988474