牛客网数据库SQL实战详细剖析(51-60)(更新完结)

这是一个系列文章，总共61题，分6期，有答案以及解题思路，并附上解题的一个思考过程。

具体题目可参考牛客网的SQL实战模块：

https://www.nowcoder.com/ta/sql?page=0

一、牛客网数据库SQL实战详细剖析(1-10)

二、牛客网数据库SQL实战详细剖析(11-20)

三、牛客网数据库SQL实战详细剖析(21-30)

四、牛客网数据库SQL实战详细剖析(31-40)

五、牛客网数据库SQL实战详细剖析(41-50)

第五十一题：查找字符串'10,A,B' 中逗号','出现的次数cnt

select (length("10,A,B") - length(replace("10,A,B", ",", ""))) as cnt;解题思路：①巧用length函数和replace，length函数计算字符串的长度，length("10,A,B")算出整个字符串的长度。②使用replace将 , 替换为空，那么整个字符串减少的长度等于 , 的长度，两者相减就是 , 出现的次数。

 第五十二题：获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列select first_namefrom employeesorder by substring(first_name, (length(first_name)-1), 2) asc;解题思路：使用字符串截取函数subtring(对象字符串,截取的起始位置,截取的字符数)，截取出最后两位字符，然后使用order by …asc进行升序排序。

 第五十三题：按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees，输出为：

select dept_no,group_concat(emp_no separator ',') as employeesfrom dept_empgroup by dept_no;

解题思路：①连接同一列字段：group_concat( [distinct] <要连接的字段> [order by 排序字段 asc/desc ] [separator '分隔符'] ) 。分隔符可以选择省略，省略时默认为逗号，这里还是写出来了。另外还有一点需要注意，group_concat函数中的各个参数之间用空格隔开，不能用逗号隔开，不然会出错。②按照dept_no进行汇总，所以要对dept_no进行分组。 

第五十四题：查找排除当前最大、最小salary之后的员工的平均工资avg_salaryselect avg(salary) as avg_salaryfrom salarieswhere salary not in (select max(salary) from salaries where to_date = '9999-01-01')and salary not in (select min(salary) from salaries where to_date = '9999-01-01')and to_date = '9999-01-01';解题思路：用两个子查询找出最大工资和最小工资，再用not in去除最大工资和最小工资，最后记得限定时间为当前。

 第五十五题：分页查询employees表，每5行一页，返回第2页的数据在SQLite中：select * from employees limit (2-1)*5,5;解题思路：limit分页公式：curPage是当前第几页；pageSize是一页多少条记录limit (curPage-1)*pageSize,pageSize参考：

https://blog.csdn.net/bandaoyu/article/details/89844673 

解题思路：（在Mysql中）每行5页，返回第2页的数据，即返回第6~10条记录，以下有两种方法可以解决：方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回。select * from employees limit 5 offset 5; 方法二：只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。select * from employees limit 5,5; 

第五十六题：获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received ，没有分配具体的员工不显示

select de.emp_no,de.dept_no,eb.btype,eb.receviedfrom dept_emp de left join emp_bonus eb on de.emp_no = eb.emp_nogroup by de.emp_no,de.dept_no;解题思路：①将dept_emp表和emp_bonus表进行左联结，左联结原因：要获取所有员工的员工编号信息，dept_emp表有全部员工的员工编号和部门编号信息。②本题的关键在于要将员工编号和部门编号进行分组。因为emp_bonus表里的员工编号不是唯一的，dept_emp表里的员工编号不是唯一的，比如有两个员工编号为10010，但是他们分别属于两个不同的部门，所以要通过两个分组进行一对一确定。

第五十七题：使用含有关键字exists查找未分配具体部门的员工的所有信息，输出格式：

select e.* from employees ewhere not exists(select * from dept_emp de where e.emp_no = de.emp_no); 解题思路：①重点理解谓词exists，谓词exists的作用是“判断是否存在满足某些条件的记录”，如果存在这样满足条件的记录，返回真，不存在，返回假。题目要查找未分配具体部门的员工的所有信息，那么，如果employees表的员工编号=dept_emp的员工编号，则表明该员工已经分配了部门。我们要的是没有分配部门的员工，所以在exists之前加个not。②输出格式为employees表的所有信息，所以employees表的所有列都要选，select e.* 

第五十八题：存在视图create view emp_v as select * from employees where emp_no >10005;获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。输出格式:

方法一：select * from emp_v;

方法二：select e.* from employees e, emp_v ev where e.emp_no = ev.emp_no;

解题思路：同四十七题。视图和表的区别在于“是否保存了实际的数据” ，在编写select语句时，不需要特别在意表和视图有什么不同，可以直接使用视图作为表进行查询 。

 第五十九题：获取有奖金的员工相关信息，给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。当前薪水表示to_date='9999-01-01' 输出格式:

 select eb.emp_no,e.first_name,e.last_name,eb.btype,s.salary,(case when eb.btype = 1 then s.salary * 0.1when eb.btype = 2 then s.salary * 0.2else s.salary * 0.3 end) as bonusfrom salaries s,emp_bonus eb,employees ewhere eb.emp_no = s.emp_noand eb.emp_no = e.emp_noand s.to_date = '9999-01-01';

解题思路：①使用case when表达式对奖金进行行列转换，这里使用的是case when <列名> then …的搜索case表达式，最后要记得使用end结束case，并将结果重命名为bonus。②使用员工编号为限定条件将三表联结查询，还有限定时间为当前。

 第六十题：按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推

select emp_no,salary,sum(salary) over(order by emp_no) as running_totalfrom salarieswhere to_date= '9999-01-01';解题思路：

①本题关键在于把sum聚合函数作为窗口函数使用，所有聚合函数都能用做窗口函数，其语法和专用窗口函数完全相同。sum(<汇总列>) over(<排序列>) as 别名；

②光看题目“前两个员工的salary累计和”不是很好理解，结合输出格式可以理解为running_total列是逐个员工的工资的累计和，每一行的工资都是前面所有行的工资总计。

③这有一个小bug，题目没有限定时间为当前，而按照输出格式来看和通过情况来看，只有限定时间为当前'9999-01-01'才能符合输出格式，才能通过，一开始考虑用员工分组，但是员工分组得到的结果并非题目本意，必须限定时间为当前。

 不使用窗口函数的方法：select s1.emp_no, s1.salary,(select sum(s2.salary) from salaries s2where s2.emp_no <= s1.emp_no and s2.to_date = '9999-01-01') as running_totalfrom salaries s1 where s1.to_date = '9999-01-01' order by s1.emp_no;解题思路：利用复表查询，以及running_total等于逐个员工的工资的累计和，即找出在表2中小于等于表1员工编号的所有员工工资进行求和，最后记得限定时间为当前，且要按照emp_no升序排序。 

第六十一题：对于employees表中，给出奇数行的first_name

select a.first_namefrom (select emp_no, first_name, row_number() over(order by first_name) as row_numfrom employees) awhere row_num % 2 = 1order by emp_no;解题思路：①窗口函数row_number的作用是赋予唯一的连续位次。巧用窗口函数row_number对数据进行行排序，对first_name进行排序，将得到的位次命名为row_num。②用求余函数找出奇数行。

--end--