sql not exists用法_牛客网数据库SQL实战详细剖析(5160)(更新完结)

文章来源：大数据肌肉猿

作者：无精疯

这是一个系列文章，总共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--

如果你觉得文章不错的话，分享、收藏、在看是对猴哥的最大支持，是猴哥持续创作的动力。

老表Pro已经满了

所以大家加老表Max吧

每日留言

说说你最近遇到的一个编程问题？

或者新学的一个小技巧？

(字数不少于15字)

完整Python基础知识要点

Python小知识 | 这些技能你不会?(一) Python小知识 | 这些技能你不会?(二) Python小知识 | 这些技能你不会?(三) Python小知识 | 这些技能你不会?(四)

近期推荐阅读：

【1】整理了我开始分享学习笔记到现在超过250篇优质文章，涵盖数据分析、爬虫、机器学习等方面，别再说不知道该从哪开始，实战哪里找了 【2】【终篇】Pandas中文官方文档：基础用法6(含1-5) 觉得不错就点一下“在看”吧