题目一:商品交易(网易校招笔试真题)
题目要求:
查找购买个数超过20,质量小于50的商品,按照商品id升序排序
表结构:
如下有一张商品表(goods),字段依次为:商品id、商品名、商品质量
id | name | weight |
1 | A1 | 100 |
2 | A2 | 20 |
3 | B3 | 29 |
4 | T1 | 60 |
5 | G2 | 33 |
6 | C0 | 55 |
还有一张交易表(trans),字段依次为:交易id、商品id、这个商品购买个数
id | goods_id | count |
1 | 3 | 10 |
2 | 1 | 44 |
3 | 6 | 9 |
4 | 1 | 2 |
5 | 2 | 65 |
6 | 5 | 23 |
7 | 3 | 20 |
8 | 2 | 16 |
9 | 4 | 5 |
10 | 1 | 3 |
运行结果示例:
思路:
我们需要先连接两张表,再根据商品id进行分组,统计每组的数量总和,需要注意我们这里要对空值进行处理,使用ifnull()函数把null转换为0。对于筛选条件也要注意,质量是非分组字段,我们直接把它放到where查询语句中即可,商品购买个数则是分组后计算出的字段,要使用having子句进行筛选。
运行代码示例:
select g.id,name,weight,sum(ifnull(count ,0)) as total
from goods g
left join trans t
on g.id = t.goods_id
where weight < 50
group by g.id
having total > 20
order by g.id
题目二:将所有获取奖金的员工当前的薪水增加10%
题目要求:
请你写出更新语句,将所有获取奖金的员工当前的(salaries.to_date='9999-01-01')薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工,不考虑获取的奖金的类型)。
表结构:
有员工获取到的奖金简表emp_bonus如下
- emp_no指获取到奖金的员工编号;
- bytpe指获取到的奖金类型。
emp_no | btype |
10001 | 1 |
有员工薪资简表salaries如下
- emp_no指员工编号;
- salary指薪资;
- from_date指该薪资的开始日期;
- to_date指该薪资的结束日期。
emp_no | salary | from_date | to_date |
10001 10001 | 85097.0 88958.0 | 2001-06-22 2002-06-22 | 2002-06-22 9999-01-01 |
运行结果示例:
思路:
这道题我们之前在题单训练中做过,注意SQL中更新语句的写法就好了。
运行代码示例:
UPDATE salaries
SET salary = salary * 1.1
where to_date = '9999-01-01' and emp_no in (select emp_no from emp_bonus)
题目三:刷题通过的题目排名
题目要求:
请你根据上表,输出通过的题目的排名,通过题目个数相同的,排名相同,此时按照id升序排列
表结构:
在牛客刷题有一个通过题目个数的(passing_number)表,id是主键,简化如下
id | number |
1 2 3 4 5 6 | 4 3 3 2 5 4 |
第1行表示id为1的用户通过了4个题目;
.....
第6行表示id为6的用户通过了4个题目;
运行结果示例:
思路:
排名问题,使用窗口函数秒杀,记得排序(rank是SQL中的关键字,使用时需要加上``)。
运行代码示例:
select id,number,dense_rank() over(order by number desc) as `rank`
from passing_number
order by `rank`,id
题目四:最差是第几名(一)
题目要求:
TM小哥和FH小妹在牛客大学若干年后成立了牛客SQL班,班的每个人的综合成绩用A,B,C,D,E表示,90分以上都是A,80~90分都是B,70~80分为C,60~70为D,E为60分以下
假设每个名次最多1个人,比如有2个A,那么必定有1个A是第1名,有1个A是第2名(综合成绩同分也会按照某一门的成绩分先后)。
每次SQL考试完之后,老师会将班级成绩表展示给同学看。
请你写出一个SQL查询,如果一个学生知道了自己综合成绩以后,最差是排第几名? 结果按照grade升序排序
表结构:
现在有班级成绩表(class_grade)如下
grade | number |
A | 2 |
D | 1 |
C | 2 |
B | 2 |
第1行表示成绩为A的学生有2个
.......
最后1行表示成绩为B的学生有2个
运行结果示例:
思路:
本题依旧是排名问题,需要我们按照排名计算总和,我们直接使用窗口函数按照成绩字段排序计算总和即可。
运行代码示例:
select grade, sum(number) over(order by grade) t_rank
from class_grade
order by grade;
题目五:考试分数(二)
题目要求:
请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性,并且以id的升序排序
表结构:
牛客每次考试完,都会有一个成绩表(grade),如下
id | job | score |
1 | C++ | 11001 |
2 | C++ | 10000 |
3 | C++ | 9000 |
4 | Java | 12000 |
5 | Java | 13000 |
6 | JS | 12000 |
7 | JS | 11000 |
8 | JS | 9999 |
9 | Java | 12500 |
第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了前端岗位并且考了9999分
运行结果示例:
思路:
本题也可以用窗口函数实现(万能的窗口函数...)
SELECT t.id,t.job,t.score FROM (SELECT *,AVG(score) OVER(PARTITION BY job) AS avg_sco FROM grade) t WHERE t.score > t.avg_sco ORDER BY t.id ASC;
另一种方法,先查询出每种岗位及其平均分数,再连接主表,筛选出主表中分数大于对应均分的数据。
运行代码示例:
select g.* from grade g
left join (select job,avg(score) as avg1 from grade group by job) t
on g.job = t.job
where g.score > t.avg1
order by g.id
题目六:获得积分最多的人(一)
题目要求:
请你写一个SQL查找积分增加最高的用户的名字,以及他的总积分是多少(此题数据保证积分最高的用户有且只有1个)
表结构:
有一个用户表(user),简况如下
id | name |
1 | tm |
2 | wwy |
3 | zk |
4 | |
5 | lm |
还有一个积分表(grade_info),简况如下
user_id | grade_num | type |
1 | 3 | add |
2 | 3 | add |
1 | 1 | add |
3 | 3 | add |
4 | 3 | add |
5 | 3 | add |
第1行表示,user_id为1的用户积分增加了3分。
第2行表示,user_id为2的用户积分增加了3分。
第3行表示,user_id为1的用户积分又增加了1分。
.......
最后1行表示,user_id为5的用户积分增加了3分。
运行结果示例:
思路:
正常情况下本题也是需要使用窗口函数求解的,但是由于题目中保证积分最高的用户有且只有1个,那我们可以偷个懒,直接分组计算出积分和再倒序排序limit查询数量为1即可。
运行代码示例:
select name,sum(ifnull(grade_num,0)) as cnt
from user u
left join grade_info g
on u.id = g.user_id
group by name
order by cnt desc
limit 1