15000 字的 MySQL 速查手册 中

3.复杂查询

查询所有课程成绩小于60分学生的学号、姓名

【知识点】子查询

1.翻译成大白话

1）查询结果：学生学号，姓名 2）查询条件：所有课程成绩 < 60 的学生，需要从成绩表里查找，用到子查询

第1步，写子查询（所有课程成绩 < 60 的学生）

select 查询结果[学号]

from 从哪张表中查找数据[成绩表：score]

where 查询条件[成绩 < 60]

group by 分组[没有]

having 对分组结果指定条件[没有]

order by 对查询结果排序[没有]

limit 从查询结果中取出指定行[没有];

select 学号 
from score
where 成绩 < 60;

第2步，查询结果：学生学号，姓名，条件是前面1步查到的学号

select 查询结果[学号,姓名]

from 从哪张表中查找数据[学生表:student]

where 查询条件[用到运算符in]

group by 分组[没有]

having 对分组结果指定条件[没有]

order by 对查询结果排序[没有]

limit 从查询结果中取出指定行[没有];

select 学号,姓名
from student
where  学号 in (
select 学号 
from score
where 成绩 < 60
);

查询没有学全所有课的学生的学号、姓名

/*
查找出学号，条件：没有学全所有课，也就是该学生选修的课程数 < 总的课程数
【考察知识点】in，子查询
*/
select 学号,姓名
from student
where 学号 in(
select 学号 
from score
group by 学号
having count(课程号) < (select count(课程号) from course)
);

查询出只选修了两门课程的全部学生的学号和姓名

select 学号,姓名
from student
where 学号 in(
select 学号
from score
group by 学号
having count(课程号)=2
);

1990年出生的学生名单

/*
查找1990年出生的学生名单
学生表中出生日期列的类型是datetime
*/
select 学号,姓名 
from student 
where year(出生日期)=1990; 

查询各科成绩前两名的记录

这类问题其实就是常见的：分组取每组最大值、最小值，每组最大的N条（top N）记录。

sql面试题：topN问题

工作中会经常遇到这样的业务问题：

如何找到每个类别下用户最喜欢的产品是哪个？

如果找到每个类别下用户点击最多的5个商品是什么？

这类问题其实就是常见的：分组取每组最大值、最小值，每组最大的N条（top N）记录。

面对该类问题，如何解决呢？

下面我们通过成绩表的例子来给出答案。

成绩表是学生的成绩，里面有学号（学生的学号），课程号（学生选修课程的课程号），成绩（学生选修该课程取得的成绩）

分组取每组最大值

案例：按课程号分组取成绩最大值所在行的数据

我们可以使用分组（group by）和汇总函数得到每个组里的一个值（最大值，最小值，平均值等）。但是无法得到成绩最大值所在行的数据。

select 课程号,max(成绩) as 最大成绩
from score 
group by 课程号;

我们可以使用关联子查询来实现：

select * 
from score as a 
where 成绩 = (
select min(成绩) 
from score as b 
where b.课程号 = a.课程号);

每组最大的N条记录