牛客网数据库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_name

from employees

order by substring(first_name, (length(first_name)-1), 2) asc;

解题思路：使用字符串截取函数subtring(对象字符串,截取的起始位置,截取的字符数)，截取出最后两位字符，然后使用order by …asc进行升序排序。

 

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

dept_no	employees
d001	10001,10002
d002	10006
d003	10005
d004	10003,10004
d005	10007,10008,10010
d006	10009,10010

select dept_no,group_concat(emp_no separator ',') as employees

from dept_emp

group by dept_no;

解题思路：

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

②按照dept_no进行汇总，所以要对dept_no进行分组。

 

第五十四题：查找排除当前最大、最小salary之后的员工的平均工资avg_salary

select avg(salary) as avg_salary

from salaries

where 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.recevied

from dept_emp de left join emp_bonus eb on de.emp_no = eb.emp_no

group by de.emp_no,de.dept_no;

解题思路：

①将dept_emp表和emp_bonus表进行左联结，左联结原因：要获取所有员工的员工编号信息，dept_emp表有全部员工的员工编号和部门编号信息。

②本题的关键在于要将员工编号和部门编号进行分组。因为emp_bonus表里的员工编号不是唯一的，dept_emp表里的员工编号不是唯一的，比如有两个员工编号为10010，但是他们分别属于两个不同的部门，所以要通过两个分组进行一对一确定。

 

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

emp_no	birth_date	first_name	last_name	gender	hire_date
10011	1953-11-07	Mary	Sluis	F	1990-01-22

select e.* from employees e

where 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关键字。输出格式:

emp_no	birth_date	first_name	last_name	gender	hire_date
10006	1953-04-20	Anneke	Preusig	F	1989-06-02
10007	1957-05-23	Tzvetan	Zielinski	F	1989-02-10
10008	1958-02-19	Saniya	Kalloufi	M	1994-09-15
10009	1952-04-19	Sumant	Peac	F	1985-02-18
10010	1963-06-01	Duangkaew	Piveteau	F	1989-08-24
10011	1953-11-07	Mary	Sluis	F	1990-01-22

方法一：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' 输出格式:

emp_no	first_name	last_name	btype	salary	bonus
10001	Georgi	Facello	1	88958	8895.8
10002	Bezalel	Simmel	2	72527	14505.4
10003	Parto	Bamford	3	43311	12993.3
10004	Chirstian	Koblick	1	74057	7405.7

 

select eb.emp_no,e.first_name,e.last_name,eb.btype,s.salary,

(case when eb.btype = 1 then s.salary * 0.1

when eb.btype = 2 then s.salary * 0.2

else s.salary * 0.3 end) as bonus

from salaries s,emp_bonus eb,employees e

where eb.emp_no = s.emp_no

and eb.emp_no = e.emp_no

and 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_total

from salaries

where 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 s2

where s2.emp_no <= s1.emp_no and s2.to_date = '9999-01-01') as running_total

from 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_name

from (select emp_no, first_name, row_number() over(order by first_name) as row_num

from employees) a

where row_num % 2 = 1

order by emp_no;

解题思路：①窗口函数row_number的作用是赋予唯一的连续位次。巧用窗口函数row_number对数据进行行排序，对first_name进行排序，将得到的位次命名为row_num。②用求余函数找出奇数行。

--end--

推荐阅读：

秋招失利，如何把握春招？(大四、研三)

计算机学生如何规划好大学四年的学习？

扫描下方二维码

添加好友，备注【交流群】

拉你到学习路线和资源丰富的交流群