【SQL每日一练】day19：常考热门题目

题目一：商品交易（网易校招笔试真题）

题目要求：

查找购买个数超过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	qq
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