文章目录

• 51* 获取Employees中的first_name
• 51.1 题目描述
• 51.2 题解 substr
• 52* 按照dept_no进行汇总
• 52.1 题目描述
• 52.2 题解 group_concat(字段名,分隔符)
• 53 查找排除当前最大、最小salary之后的员工的平均工资avg_salary
• 53.1 题目描述
• 53.2 题解
• 54 分页查询employees表，每5行一页，返回第2页的数据
• 54.1 题目描述
• 54.2 题解 limit offset
• 55 获取所有员工的emp_no
• 55.1 题目描述
• 55.2 题解
• 56* 使用含有关键字exists查找未分配具体部门的员工的所有信息。
• 56.1 题目描述
• 56.2 题解 exists
• 57 获取employees中的行数据，且这些行也存在于emp_v中
• 57.1 题目描述
• 57.2 题解
• 58 获取有奖金的员工相关信息。
• 58.1 题目描述
• 58.2 题解 case..when..then..else..end
• 59* 统计salary的累计和running_total
• 59.1 题目描述
• 59.2 题解
• 60* 对于employees表中，给出奇数行的first_name
• 60.1 题目描述
• 60.2 题解

欢迎访问笔者个人技术博客： http://rukihuang.xyz/
牛客网数据库SQL实战
51* 获取Employees中的first_name 51.1 题目描述 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));

输出格式：

first_name
Chirstian
Tzvetan
Bezalel
Duangkaew
Georgi
Kyoichi
Anneke
Sumant
Mary
Parto
Saniya

51.2 题解 substr

select first_name from employees order by substr(first_name, length(first_name)-1,length(first_name))

• 本题考查 substr(X,Y,Z)或substr(X,Y)函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

52* 按照dept_no进行汇总 52.1 题目描述 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`));

输出格式:

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

52.2 题解 group_concat(字段名,分隔符)

select dept_no ,group_concat(emp_no, ",") from dept_emp group by dept_no

• 本题要用到SQLite的聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。

53 查找排除当前最大、最小salary之后的员工的平均工资avg_salary 53.1 题目描述 查找排除当前最大、最小salary之后的员工的平均工资avg_salary。

CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`));

输出格式:

avg_salary
69462.5555555556

53.2 题解

• 正常逻辑，OJ过不了

select avg(salary) as avg_salary from salaries where salary not in( -- 1 找到最高工资和最小工资的salary (select max(salary) from salaries where to_date = '9999-01-01') -- OJ中需要把时间条件删除，才能过 ,(select min(salary) from salaries where to_date = '9999-01-01') -- OJ中需要把时间条件删除，才能过 ) and to_date = '9999-01-01'

• OJ通过题解

select avg(salary) as avg_salary from salaries where salary not in( -- 1 找到最高工资和最小工资的salary (select max(salary) from salaries ) ,(select min(salary) from salaries ) ) and to_date = '9999-01-01'

54 分页查询employees表，每5行一页，返回第2页的数据 54.1 题目描述 分页查询employees表，每5行一页，返回第2页的数据

CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));

54.2 题解 limit offset

• 方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回==（第一条记录序号为0）==，也可理解为跳过多少条记录后开始返回。

select * from employees limit 5 -- 返回5条数据 offset 5 -- 从第6条数据开始返回

• 只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。

select * from employees limit 5,5 -- 从第6条记录开始，返回4条数据（第1条数据是第0条数据）

55 获取所有员工的emp_no 55.1 题目描述 获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received ，没有分配具体的员工不显示

CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`)); CREATE TABLE `dept_manager` ( `dept_no` char(4) NOT NULL, `emp_no` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`)); CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`)); CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL,PRIMARY KEY (`emp_no`,`from_date`)); create table emp_bonus( emp_no int not null, received datetime not null, btype smallint not null);

输出格式:

e.emp_no	dept_no	btype	received
10001	d001	1	2010-01-01
10002	d001	2	2010-10-01
10003	d004	3	2011-12-03
10004	d004	1	2010-01-01
10005	d003
10006	d002
10007	d005
10008	d005
10009	d006
10010	d005
10010	d006

55.2 题解

select d.emp_no ,d.dept_no ,e.btype ,e.recevied -- 这个字段是题目打错的，我日，建议击毙 from dept_emp d left join emp_bonus e on d.emp_no = e.emp_no

• 正常题解：本题严谨的思路为，先将 employees与dept_emp 用 INNER JOIN 连接，挑选出分配了部门的员工，再用 LEFT JOIN 连接 emp_bonus（在前面的题中可看到此表），分配了奖金的员工显示奖金类型和授予时间，没分配奖金的员工则不显示。

SELECT em.emp_no , de.dept_no , eb.btype , eb.recevied FROM employees AS em INNER JOIN dept_emp AS de ON em.emp_no = de.emp_no LEFT JOIN emp_bonus AS eb ON de.emp_no = eb.emp_no

56* 使用含有关键字exists查找未分配具体部门的员工的所有信息。 56.1 题目描述 使用含有关键字exists查找未分配具体部门的员工的所有信息。

CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`)); CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`));

【数据库|牛客数据库SQL实战 51-60（substr切割字符串、group_concat组拼接、limit_offset分页、exists条件成立判断、case分支、表的复用）】输出格式:

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

56.2 题解 exists

• 不使用exists

select * from employees where emp_no not in ( select emp_no from dept_emp d )

• 使用exists

select * from employees where not exists ( select emp_no from dept_emp where emp_no = employees.emp_no -- 外面的表一定要和存在条件的子表差生关联 )

SELECT * FROM employees WHERE emp_no NOT exists ( SELECT emp_no FROM dept_emp)-- 过不了，在 employees 中没有一条记录能使 (SELECT emp_no FROM dept_emp) 不成立。(SELECT emp_no FROM dept_emp) 没有跟 emplotees 产生联系，因为无论选中 employees 中的哪条记录，(SELECT emp_no FROM dept_emp) 都成立

57 获取employees中的行数据，且这些行也存在于emp_v中 57.1 题目描述 存在如下的视图：

create view emp_v as select * from employees where emp_no >10005; CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));

获取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

57.2 题解

-- 这题真的智障 select * from emp_v

58 获取有奖金的员工相关信息。 58.1 题目描述 获取有奖金的员工相关信息。

CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`)); CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`)); create table emp_bonus( emp_no int not null, recevied datetime not null, btype smallint not null); CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`));

给出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

58.2 题解 case…when…then…else…end

select em.emp_no ,em.first_name ,em.last_name ,eb.btype ,s.salary ,(case eb.btype when 1 then 0.1 * s.salary when 2 then 0.2 * s.salary else 0.3 * s.salary end ) as bonus from employees em left join emp_bonus eb on em.emp_no = eb.emp_no left join salaries s on eb.emp_no = s.emp_no where s.to_date = '9999-01-01'

59* 统计salary的累计和running_total 59.1 题目描述 按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。

CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`));

输出格式:

emp_no	salary	running_total
10001	88958	88958
10002	72527	161485
10003	43311	204796
10004	74057	278853
10005	94692	373545
10006	43311	416856
10007	88070	504926
10009	95409	600335
10010	94409	694744
10011	25828	720572

59.2 题解 本题的思路为复用 salaries 表进行子查询，最后以 s1.emp_no 排序输出求和结果。
1、输出的第三个字段，是由一个 SELECT 子查询构成。将子查询内复用的 salaries 表记为 s2，主查询的 salaries 表记为 s1，当主查询的 s1.emp_no 确定时，对子查询中不大于 s1.emp_no 的 s2.emp_no 所对应的薪水求和
2、注意是对员工当前的薪水求和，所以在主查询和子查询内都要加限定条件 to_date = ‘9999-01-01’

select s2.emp_no ,s2.salary ,( select sum(salary) from salaries s1 where s1.emp_no <= s2.emp_no -- 复用两张表 and s1.to_date = '9999-01-01' ) as running_total from salaries s2 where s2.to_date = '9999-01-01'

60* 对于employees表中，给出奇数行的first_name 60.1 题目描述 对于employees表中，给出奇数行的first_name

CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`));

输出格式:

first_name
Georgi
Chirstian
Anneke
Tzvetan
Saniya
Mary

60.2 题解

SELECT E1.first_name FROM employees E1 WHERE ( SELECT COUNT(*)-- 序号 复用2张表，先对first_name进行排序，count(*)就是序号 FROM employees E2 WHERE E1.first_name >= E2.first_name) % 2 = 1;

推荐阅读

• 

  水果电商为什么这么多假货 水果电商为什么这么多，水果电商为什么这么多广告
• 

  佳能相机能摄影吗 为什么报摄影师都用佳能相机
• 

  如何摆脱过敏体质？ 过敏体质
• 

  赖氏古夷苏木家具怎么样 赖氏家具怎么样
• 

  秘制沙茶酱制作方法大全 秘制沙茶酱制作方法
• 

  Win10添加pin码报错“0x80190001”如何解决？
• 

  西门子燃气灶微动开关故障维修
• 

  冬天有什么水果成熟，冬天的应季水果有哪些知乎
• 

  绘本讲师训练营【17期】13/21阅读原创——《苏菲的杰作》
• 

  黑农48大豆品种介绍，产量如何 黑农84大豆种简介
• 

  如何快速学习韩语
• 

  拿到一款刚买的数码相机。如何检测呢？
• 

  千百块可靠吗 千百块是真的吗 千百块平台介绍
• 

  加湿器的雾变小了是什么原因
• 

  摩托车链条用黄油润滑行不行？使用黄油润滑有何危害？
• 

  不喜欢白天，喜欢晚上
• 

  面部如何控油护肤 面部如何控油，面部如何控油补水
• 

  梦到老婆拿刀来砍我有什么预兆 梦到老婆拿刀来砍我有什么预兆解梦
• 

  如何管理一个营销主管部门，营销管理的方式
• 

  当年万里觅封侯典故 当年万里觅封侯典故是怎么样的呢

• 数据库|SQL行转列方式优化查询性能实践
• mysql|一文深入理解mysql
• 达梦数据库|DM8表空间备份恢复
• 数据技术|一文了解Gauss数据库（开发历程、OLTP&OLAP特点、行式&列式存储，及与Oracle和AWS对比）
• SqlServer|sql server的UPDLOCK、HOLDLOCK试验
• 谈灾难恢复指标（RTO与RPO是什么鬼（））
• RPO与RTO
• 数据库|效率最高的Excel数据导入---（c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】）...