文章来源:大数据肌肉猿
作者:无精疯
这是一个系列文章,总共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--
如果你觉得文章不错的话,分享、收藏、在看是对猴哥的最大支持,是猴哥持续创作的动力。
![9d13d2af861f6d0ebcb107c4513f39f4.png](https://i-blog.csdnimg.cn/blog_migrate/7b2827246cb20c1fc171e6d60adef11e.jpeg)
老表Pro已经满了
所以大家加老表Max吧
每日留言
说说你最近遇到的一个编程问题?
或者新学的一个小技巧?
(字数不少于15字)
完整Python基础知识要点
Python小知识 | 这些技能你不会?(一)
Python小知识 | 这些技能你不会?(二)
Python小知识 | 这些技能你不会?(三)
Python小知识 | 这些技能你不会?(四)
近期推荐阅读:
【1】整理了我开始分享学习笔记到现在超过250篇优质文章,涵盖数据分析、爬虫、机器学习等方面,别再说不知道该从哪开始,实战哪里找了
【2】【终篇】Pandas中文官方文档:基础用法6(含1-5)
觉得不错就点一下“在看”吧