sql分组取出每组最大一条_SQL面试题

一.简单查询

查询姓“孟”老师的个数

二.汇总分析

练习：汇总分析

1.查询课程编号为“0002”的总成绩

/*select查询结果（总成绩：sum）
from哪张表（score）
where查询条件（课程号0002）
*/ 

2.查询选了课的学生人数

/*select查询结果（学号去掉重复 学生人数：count）
from哪张表（score）
*/

练习：分组

1.查询各科成绩最高和最低的分

/*select查询结果（课程号：分组，最高成绩max，最低成绩min）
from成绩表（score）
where 没有
group by（每门课程：课程号分组）
*/

2.查询每门课程被选修的学生数

/*select查询结果（课程号，学生人数（count））
from 成绩表score
where没有
group by（课程号分组） 
*/

3.查询男生，女生人数

/*select查询结果（人数：count）
from学生表student
where没有
group by（性别分组） 

练习：分组结果的条件

1.查询平均成绩大于60分学生的学号

/*select查询结果（学号，平均成绩:avg（成绩））
from成绩表score
where没有
group by学号
having对分组结果指定条件（平均成绩大于60）

2.查询至少选修两门课程的学生学号

/*select查询结果（学号，每个学生选修的课程数目：count）
from成绩表
where没有
groupby 学号
having 课程号>=2

3.查询同名同姓学生名单并统计同名人数

/*select （姓名，同名人数：count）
from 学生表
where没有
group by 姓名
having 姓名>=2

4.查询不及格的课程并按课程号从大到小排列

/*select查询结果（课程号，不及格课程：成绩）
from成绩单score
where成绩<60
order by 课程号

5.查询每门课的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

/*select 平均成绩，课程号
from成绩表
where没有
groupby 课程号
having 没有
order by（平均成绩升序：asc，平均成绩相同课程号降序：desc）

6.检索课程编号为“0004”且分数小于60的学生学号，结果按分数降序排列

/*select查询结果（学号）
from成绩表
where课程号为0004并且分数小于60
order by分数降序

7.统计每门课程的学生选修人数（超过2人的课程才统计）要求输出课程号和选课人数，查询结果按人数降序排列，若人数相同，按课程号升序排序

/*select 选修人数：count，课程号
from成绩表
where没有
group by课程号
having选修人数>2
order by人数降序，人数相同升序

8.查询两门以上不及格课程的学生学号，以及不及格课程的平均成绩

/*select查询结果（学号，平均成绩）
from成绩表
where不及格的课程，不及格的成绩
group by学号
having课程数目>2

三.复杂查询

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

/*SELECT 学号，姓名
from成绩表，学生表
where成绩小于60，子查询：成绩表

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

/*select 学号 姓名
from学生表，成绩表，课程表
where选修的课程小于总课程数目

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

/*select 学号 姓名
from学生表，成绩表，课程表
where选修的课程=2门

4.1990年出生的学生名单

日期函数

【面试题类型】topN问题

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

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

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

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

1.按课程号分组取成绩最大值所在行的数据

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

2.分组取最小值，用关联子查询实现

3.每组最大的N条记录：查询各科成绩前两名的记录

第1步，查出有哪些组：我们可以按课程号分组，查询出有哪些组，对应这个问题里就是有哪些课程号

第2步：先使用order by子句按成绩降序排序（desc），然后使用limt子句返回topN（对应这个问题返回的成绩前两名）

-- 课程号'0001' 这一组里成绩前2名

第3步，使用union all 将每组选出的数据合并到一起

四.多表查询

1.下面是学生的名单，表名为“学生表”；近视学生的名单，表名为“近视学生表”。请问不是近视眼的学生都有谁？（“学生表”表中的学号与“近视学生”表中的学生学号一一对应）

属于左联结

2. 某网站包含两个表，顾客姓名表（表名Customers）和 购买记录表（表名Orders）。找出所有从不订购任何东西的客户。（“顾客姓名表”中的ID与“购买记录”表中的学生学号CustomerId一一对应）

练习：联结

查询所有学生的学号、姓名、选课数、总成绩

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

查询学生的选课情况：学号、姓名、课程号、课程名称

查询出每门课程的及格人数和不及格人数

使用分段[100-85]，[85-70]，[70-60]，[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

以上问题答案参考

R13：mysql：多表查询​zhuanlan.zhihu.com

1.查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名

2.下面是学生的成绩表（表名score，列名：学号、课程号、成绩）

使用sql实现将该表行转列为下面的表结构

这类题目属于行列如何互换，解题思路如下：

1）第1步，使用常量列输出目标表的结构,可以看到查询结果已经和目标表非常接近了

2）第2步，使用case表达式，替换常量列为对应的成绩

在这个查询结果中，每一行表示了某个学生某一门课程的成绩。比如第一行是'学号0001'选修'课程号00001'的成绩，而其他两列的'课程号0002'和'课程号0003'成绩为0。每个学生选修某门课程的成绩在下图的每个方块内。我们可以通过分组，取出每门课程的成绩。

3）第3步，分组，并使用最大值函数max取出上图每个方块里的最大值

五.如何提高sql查询效率

数据量大时，不同的sql语句消耗的时间相差很大，写SQL语句时需要注意以下几点以提高查询效率：

• select子句中尽量避免使用*。
• SQL 语句句比较符号的左侧避免使用函数。因为这会导致数据库引擎进行全表扫描，从而增加运行时间。所以，为了提高效率，where子句中遇到函数或加减乘除的运算，应当将其移到比较符号的右侧。
• 避免使用in和not in。in和not in也会导致数据库进行全表搜索，增加运行时间。
• 尽量避免使用or。or同样会导致数据库进项全表搜索。可以考虑用联结优化，子句有些长，但是运行起来却会很节省时间
• 尽量用limit子句限制返回的数据行数。如果前台只需要显示15行数据，而你的查询结果集返回了1万行，那么这适合最好使用limt子句来限制查询返回的数据行数。