有時會碰到一些需求眷柔，查詢分組后的最大值期虾，最小值所在的整行記錄或者分組后的top n行的記錄，在一些別的數(shù)據(jù)庫可能有窗口函數(shù)可以方面的查出來驯嘱，但是MySQL沒有這些函數(shù),沒有直接的方法可以查出來镶苞，可通過以下的方法來查詢。
準(zhǔn)備工作
測試表結(jié)構(gòu)如下：
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)
插入數(shù)據(jù)：
insert into test1(name,course,score)
values
('張三','語文',80),
('李四','語文',90),
('王五','語文',93),
('張三','數(shù)學(xué)',77),
('李四','數(shù)學(xué)',68),
('王五','數(shù)學(xué)',99),
('張三','英語',90),
('李四','英語',50),
('王五','英語',89);
查看結(jié)果：
root:test> select * from test1;
+----+--------+--------+-------+
| id | name | course | score |
+----+--------+--------+-------+
| 1 | 張三 | 語文 | 80 |
| 2 | 李四 | 語文 | 90 |
| 3 | 王五 | 語文 | 93 |
| 4 | 張三 | 數(shù)學(xué) | 77 |
| 5 | 李四 | 數(shù)學(xué) | 68 |
| 6 | 王五 | 數(shù)學(xué) | 99 |
| 7 | 張三 | 英語 | 90 |
| 8 | 李四 | 英語 | 50 |
| 9 | 王五 | 英語 | 89 |
+----+--------+--------+-------+
TOP 1
查詢每門課程分數(shù)最高的學(xué)生以及成績
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 |
| 王五 | 數(shù)學(xué) | 99 |
| 張三 | 英語 | 90 |
+--------+--------+-------+
3 rows in set (0.00 sec)
2茂蚓、使用相關(guān)子查詢
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 |
| 王五 | 數(shù)學(xué) | 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 |
| 王五 | 數(shù)學(xué) | 99 |
| 張三 | 英語 | 90 |
+--------+--------+-------+
3 rows in set (0.00 sec)
TOP N
N>=1
查詢每門課程前兩名的學(xué)生以及成績
1、使用union all
如果結(jié)果集比較小谢澈，可以用程序查詢單個分組結(jié)果后拼湊煌贴，也可以使用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='數(shù)學(xué)' 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 |
| 王五 | 數(shù)學(xué) | 99 |
| 張三 | 數(shù)學(xué) | 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 |
+--------+--------+-------+
| 王五 | 數(shù)學(xué) | 99 |
| 張三 | 數(shù)學(xué) | 77 |
| 張三 | 英語 | 90 |
| 王五 | 英語 | 89 |
| 王五 | 語文 | 93 |
| 李四 | 語文 | 90 |
+--------+--------+-------+
6 rows in set (0.00 sec)
3锥忿、相關(guān)子查詢
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 | 王五 | 數(shù)學(xué) | 99 |
| 4 | 張三 | 數(shù)學(xué) | 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 |
+--------+--------+-------+
| 王五 | 數(shù)學(xué) | 99 |
| 張三 | 數(shù)學(xué) | 77 |
| 張三 | 英語 | 90 |
| 王五 | 英語 | 89 |
| 王五 | 語文 | 93 |
| 李四 | 語文 | 90 |
+--------+--------+-------+
6 rows in set (0.00 sec)
