![8e3408636248096c1e3b5887e35c7189.png](https://i-blog.csdnimg.cn/blog_migrate/a19f064e2becb06f2658409fb586692f.jpeg)
题目同样来自开心鸭专栏,侵权删除
1.学生表:tb_student(name:学生姓名,id:学号,class:班级,in_time:入学时间,age:年龄,sex:性别,major:专业),学生成绩表:tb_score(id:学号,course:课程,score:分数)
我把它转换成列表形式方便理解:
![0b1f3a466a47d2b9773c69be3853f3c3.png](https://i-blog.csdnimg.cn/blog_migrate/263253e0ca3c232ab5533e81d9242ec5.png)
1. 1筛选出2017年入学的“计算机”专业年龄最小的10位同学名单(姓名、学号、班级、年龄)
select name,id,class,age from tb_student
where in_time='2017 and major='计算机'
order by age #(不写就是默认从小到大)
limit 10
第一题很简单,考察了条件筛选,排序,还有保留
1.2.统计每个班同学各科成绩平均分大于80分的人数和人数占比
select a.class,count(a.id) as 人数,
count(a.id)/(select count(c.id) from tb_student c where a.class=c.class) as 占比
from tb_student a
where 80<(select avg(b.score) from tb_score as b
where a.id=b.id)
group by a.class
考察表联结和关联子查询
3.用户教育经历表:tb_user_edu(uid:用户id,star_date:入学时间,end_date:毕业时间,degree:学历,school:学校,major:专业)
这题没有原题,自己出一题,每个专业毕业最早的用户是谁
考察窗口函数
select x.uid from
(select uid,row_number() over (partition by major order by end_time) as ranking from tb_user_edu) as x
having x.row_number=1
4.table1(id:自增id,money:费用)问题:按id顺序累加money,取出累计值与1000相差最小差值的id
select id from
(select id,sum(money) over (order by id asc) as 累计值 from table1) as c
order by abs(1000-累计值) asc
limit 1
窗口函数的考察
5.Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 DepartmentId。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询,找出每个部门工资第二高的员工。
先表联结再窗口函数排序
#但是有比较要注意的问题,两张表中都有重复的列名Name,因此查询的时候直接改成别名,否则会报错,另外注意partition by和order by 之间没有逗号,空格即可
select 部门,姓名 from (select b.name as 部门,a.Name as 姓名,
row_number() over (partition by b.Id order by a.salary desc) as ranking
from Employee as a inner join Department b on a.DepartmentId=b.Id) as c
where ranking=2
总结:二面重点考察了c窗口函数和表联结,不会的同学可以看我之前的文章
优梨:SQL高级功能:窗口函数zhuanlan.zhihu.com