牛客网SQL实战二刷 | Day9

「牛客网SQL实战二刷」是个系列学习笔记博文，每天解析6道SQL题目～ 今天是第49 - 54题。

每篇笔记的格式大致为，三大板块：

• 大纲
• 题目（题目描述、思路、代码、相关参考资料/答疑）
• 回顾

❤️「往期回顾」

《牛客网SQL实战二刷 | 完整解析 – 目录索引》

---

一、 大纲

题号	知识点
49	SQLITE_MASTER， 字符连接
50	字符链接
51	length（），REPLACE（）
52	Substr（）
53	group_concat() ，分组的字符连接
54	MAX（），MIN（），AVG（）

---

二、题目

49. 针对库中的所有表生成select count(*)对应的SQL语句

• 题目描述

针对库中的所有表生成select count(*)对应的SQL语句

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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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 `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`));

• 输出描述

cnts
select count(*) from employees;
select count(*) from departments;
select count(*) from dept_emp;
select count(*) from dept_manager;
select count(*) from salaries;
select count(*) from titles;
select count(*) from emp_bonus;

• 思路

本题主要有以下两个关键点：

• 1、在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，而且对于自己创建的表而言，字段 type 永远是 ‘table’。
• 2、在 SQLite 中用 “||” 符号连接字符串

作者：wasrehpic
来源https://www.nowcoder.com/questionTerminal/355036f7f0c8429a85281f7ac05b457a?f=discussion

• 代码

SELECT "select count(*) from " || name || ";" AS cnts
FROM SQLITE_MASTER 
WHERE type = 'table'

?SQLITE_MASTER

SQLite数据库中一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有表的索引。每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表， 它定义数据库的模式。

SQLITE_MASTER 表看起来如下：

CREATE TABLE sqlite_master ( 
type TEXT, 
name TEXT, 
tbl_name TEXT, 
rootpage INTEGER, 
sql TEXT 
); 

对于表来说，type 字段永远是 ‘table’，name 字段永远是表的名字。所以，要获得数据库中所有表的列表， 使用下列SELECT语句：

SELECT name FROM sqlite_master 
WHERE type=’table’ 
ORDER BY name; 

（来源：https://www.cnblogs.com/lvdongjie/p/7813297.html ）

---

50. 将employees表中的所有员工的last_name和first_name通过(’)连接起来。

• 题目描述

将employees表中的所有员工的last_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`));

• 输出格式

name
Facello’Georgi
Simmel’Bezalel
Bamford’Parto
Koblick’Chirstian
Maliniak’Kyoichi
Preusig’Anneke
Zielinski’Tzvetan
Kalloufi’Saniya
Peac’Sumant
Piveteau’Duangkaew
Sluis’Mary

• 思路
  用 || 连接；

• 代码

SELECT last_name || "'" || first_name FROM employees AS name

---

51. 查找字符串’10,A,B’ 中逗号’,'出现的次数cnt

• 题目描述

查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。

• 思路

• 由于 SQLite 中没有直接统计字符串中子串出现次数的函数，因此本题用length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题，属于技巧题，即先用replace函数将原串中出现的子串用空串替换，再用原串长度减去替换后字符串的长度，最后除以子串的长度（本题中此步可省略，若子串长度大于1则不可省）。

作者：wasrehpic
来源：https://www.nowcoder.com/questionTerminal/e3870bd5d6744109a902db43c105bd50?f=discussion

• 代码

SELECT (length('10,A,B') - length(REPLACE('10,A,B',",",""))) AS cnt;

---

52. 获取Employees中的first_name

• 题目描述

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

• 代码

SELECT first_name FROM employees
ORDER BY SUBSTR(first_name, -2)

?SUBSTR()

• 1.作用:
  用来截取数据库某个字段中的一部分。
• 2、语法：substr(string,start,length)
  （1）string参数：必选。数据库中需要截取的字段。
  （2）start参数：必选。正数，从字符串指定位子开始截取；负数，从字符串结尾指定位子开始截取；0，在字符串中第一个位子开始截取。1，同理。(特殊)
  （3）length参数：可选。需要截取的长度。缺省，即截取到结束位置。
• 3、简单示例：
  （1）substr(‘123456’,3,2); 结果是–34
  （2）substr(‘123456’,-3,2); 结果是–45
  （3）substr(‘123456’,0,2); 特殊，与下同
  （4）substr(‘123456’,1,2); 结果同为–12
• 4、补充：在各个数据库的函数名称略有差异。
  （1）MySQL–substr()或substring()
  （2）Oracle–substr()
  （3）SQL Server–substring()

来源：https://blog.csdn.net/l358366885/article/details/79973430

• 强烈参考

《SQL函数–substr()》https://blog.csdn.net/l358366885/article/details/79973430

---

53. 按照dept_no进行汇总

• 题目描述

按照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

• 思路

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

作者：wasrehpic
来源：https://www.nowcoder.com/questionTerminal/6e86365af15e49d8abe2c3d4b5126e87?f=discussion

• 代码

SELECT dept_no, GROUP_CONCAT(emp_no, ',') AS employees
FROM dept_emp
GROUP BY dept_no

?group_concat()函数

• 1、功能：
  将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
• 2、语法：
  group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
• 3、 说明：
  通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

来源：https://www.cnblogs.com/rxhuiu/p/9134009.html

• 参考资料

《SQL里的concat() 以及group_concat() 函数的使用》https://www.cnblogs.com/rxhuiu/p/9134009.html

---

54. 查找排除当前最大、最小salary之后的员工的平均工资avg_salary

• 题目描述

查找排除当前最大、最小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

• 思路

• 本题逻辑有问题，在挑选当前最大、最小salary时没加 to_date = ‘9999-01-01’ 作条件限制，导致挑选出来的是全表最大、最小salary，然后对除去这两个salary再作条件限制 to_date = ‘9999-01-01’ ，求平均薪水，此时求出的平均薪水与题目逻辑要求的不同。

作者：wasrehpic
来源：https://www.nowcoder.com/questionTerminal/95078e5e1fba4438b85d9f11240bc591?f=discussion

• 代码

SELECT AVG(salary) AS avg_salary FROM salaries 
WHERE to_date = '9999-01-01' 
AND salary NOT IN (SELECT MAX(salary) FROM salaries)
AND salary NOT IN (SELECT MIN(salary) FROM salaries)

正确的逻辑应如下所示，但在本题OJ系统中通不过：

SELECT AVG(salary) AS avg_salary FROM salaries 
WHERE to_date = '9999-01-01' 
AND 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')

---

三、回顾

Day9 主要都是「函数」和「字符连接」啊～

知识点	题号
字符连接	49，59，53
函数	51「length（），REPLACE（）」，52「Substr（）」，53「group_concat()」，54「MAX（），MIN（），AVG（）」