[牛客数据库SQL实战] 51~61题及个人解答

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

-- 除去','之前字符长度
SELECT LENGTH('10,A,B') AS length_str;
-- 除去','之后字符长度
SELECT LENGTH(REPLACE('10,A,B', ',','')) AS length_only_str;
-- 逗号出现次数
SELECT (LENGTH('10,A,B') - LENGTH(REPLACE('10,A,B', ',',''))) AS cnt;

运行时间：17ms

占用内存：3408k

【mysql】将以固定分隔符分隔的字符串转成多行的形式
length(tagids)-length(replace(tagids,',','')) 表示tagsid含有多少个标点符号，那标点符号+1就表示tagids用","分隔符分隔后含有多少个元素，即标签个数。

52. 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

-- 使用substr(s,start,len)函数 截取first_name最后两个字母 并以此排序
SELECT DISTINCT first_name
FROM employees
ORDER BY SUBSTR(first_name, LENGTH(first_name)-1, 2);

运行时间：28ms

占用内存：6628k

在MySQL中，还可以使用RIGHT(s,n)函数

-- 使用RIGHT(s,n)函数 获取first_name最后两个字母 并以此进行排序
SELECT DISTINCT first_name
FROM employees
ORDER BY RIGHT(first_name, 2);

MySQL 函数

RIGHT(s,n)
返回字符串 s 的后 n 个字符

SUBSTR(s,start,len)
从字符串 s 的 start 位置截取长度为 len 的子字符串

LENGTH(s)/CHAR_LENGTH(s)
返回字符串 s 的字符数

53. 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

-- group_concat()函数的基本使用
SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp
GROUP BY dept_no

运行时间：18ms

占用内存：3300k

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

-- 选出最大、最小薪水 再排除
SELECT AVG(salary) AS avg_salary
FROM salaries AS s, (
	SELECT MAX(salary) AS max_sal, MIN(salary) AS min_sal  FROM salaries
) AS ms
WHERE to_date='9999-01-01' AND s.salary != ms.max_sal AND s.salary != ms.min_sal;

运行时间：18ms

占用内存：3548k

-- 以下方法更严谨 
-- 选取最值时 是在当前选择的 而不是全局 
-- 但过不了OJ审核。。。
SELECT AVG(salary) AS avg_salary
FROM salaries AS s, (
	SELECT MAX(salary) AS max_sal, MIN(salary) AS min_sal  
    FROM salaries
    WHERE to_date='9999-01-01'
) AS ms
WHERE to_date='9999-01-01' AND s.salary != ms.max_sal AND s.salary != ms.min_sal;

55. 分页查询employees表，每5行一页，返回第2页的数据

-- 使用limit来实现分页
-- 第一页 以5行为一页 0,1,2,3,4
SELECT * FROM employees LIMIT (1-1)*5,5;
-- 第二页	5,6,7,8,9
SELECT * FROM employees LIMIT (2-1)*5,5;

运行时间：27ms

占用内存：4564k

MySQL的limit用法和分页查询的性能分析及优化

56. 获取所有员工的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 `emp_bonus`(
	`emp_no` int NOT NULL,
	`recevied` datetime NOT NULL,
	`btype` smallint NOT NULL);

-- 因为没有分配的员工不显示 所以员工表只取dept_emp就行
-- 注意emp_bonus 以前所建立的表
SELECT de.emp_no, de.dept_no, eb.btype, eb.recevied
FROM dept_emp AS de
LEFT JOIN emp_bonus AS eb
ON de.emp_no=eb.emp_no;

运行时间：22ms

占用内存：3368k

57. 使用含有关键字exists查找未分配具体部门的员工的所有信息。

-- exists()就类似一个函数 
-- 输入de.emp_no 输出是否存在'e.emp_no=de.emp_no'等式成立 返回T/F
SELECT *
FROM employees AS e
WHERE NOT EXISTS (
	SELECT * 
    FROM dept_emp AS de 
    WHERE e.emp_no=de.emp_no
);

运行时间：20ms

占用内存：3320k

用 NOT EXISTS 关键字的方法如下：
意在 employees 中挑选出令(SELECT * FROM dept_emp WHERE emp_no = employees.emp_no)不成立的记录

EXISTS对外表用loop逐条查询，每次查询都会查看EXISTS的条件语句，
当 EXISTS里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录;
反之如果EXISTS里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，
EXISTS的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false。

58. 获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。

这是题目所给的前置条件

存在如下的视图：
create view emp_v as select * from employees where emp_no >10005;

与第47题一样，以下解法都能通过OJ系统审核

-- 最直观的 emp_v只是在employees的基础上生成的
SELECT * FROM emp_v;

-- 使用where
SELECT em.*
FROM employees AS em, emp_v AS ev 
WHERE em.emp_no=ev.emp_no;

-- 使用连接 找交集
SELECT em.*
FROM employees AS em 
INNER JOIN emp_v AS ev 
ON em.emp_no=ev.emp_no;

59. 获取有奖金的员工相关信息。给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。

bonus类型

btype为1 其奖金为薪水salary的10%，

btype为2 其奖金为薪水的20%，

其他类型均为薪水的30%。

当前薪水表示to_date='9999-01-01'

-- 获奖员工信息 在使用btype时，除于10.0 
-- 注意要除以10.0，如果除以10的话，结果的小数位会被舍去
SELECT e.emp_no, e.first_name, e.last_name, eb.btype, s.salary, (s.salary*eb.btype/10.0) AS bonus
FROM employees AS e 
INNER JOIN emp_bonus AS eb
ON e.emp_no=eb.emp_no
INNER JOIN salaries AS s 
ON eb.emp_no=s.emp_no AND s.to_date='9999-01-01';

运行时间：20ms

占用内存：3408k

-- 通过case when使用btype
SELECT e.emp_no, e.first_name, e.last_name, eb.btype, s.salary, (
	CASE eb.btype
		WHEN 1 THEN s.salary*0.1
		WHEN 2 THEN s.salary*0.2
		ELSE s.salary*0.3
	END) AS bonus
FROM employees AS e 
INNER JOIN emp_bonus AS eb
ON e.emp_no=eb.emp_no
INNER JOIN salaries AS s 
ON eb.emp_no=s.emp_no AND s.to_date='9999-01-01';

运行时间：21ms

占用内存：3300k

60. 按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。

具体结果如下Demo展示。

输出格式:

emp_no	salary	running_total
10001	88958	88958
10002	72527	161485
10003	43311	204796

-- 通过group by定位当前计算的emp_no 分组内容为比其emp_no小的员工 然后进行累加
SELECT s1.emp_no, s1.salary, SUM(s2.salary)
FROM salaries AS s1, salaries AS s2
WHERE s1.to_date='9999-01-01' AND s2.to_date='9999-01-01'
AND s2.emp_no <= s1.emp_no
GROUP BY s1.emp_no, s1.salary;

运行时间：29ms

占用内存：3572k

-- 使用子查询 更加直观
SELECT s1.emp_no, s1.salary, 
	(SELECT SUM(s2.salary) 
	FROM salaries AS s2 
	WHERE s2.emp_no <= s1.emp_no AND s2.to_date = '9999-01-01') AS running_total 
FROM salaries AS s1 
WHERE s1.to_date = '9999-01-01' 
ORDER BY s1.emp_no;

运行时间：21ms

占用内存：3304k

注意：以上两种方法，虽然在实际环境上没有报错，但过于缓慢，没有做到缓存之前的结果
以下使用临时变量@persum的方式高效的达到成果 5ms (+2ms)

-- 使用@persum作为临时变量 存储上一次累加的薪水总额
SELECT s.emp_no, s.salary, (@persum:=@persum + s.salary) AS running_total
FROM salaries AS s, (SELECT @persum:=0) AS ps
WHERE s.to_date='9999-01-01'
ORDER BY s.emp_no;

61. 对于employees表中，给出奇数行的first_name

-- 获取排名为奇数的first_name
SELECT e1.first_name 
FROM (
	-- 得到每个first_name和它的排名	
	SELECT e2.first_name, (
		-- 对每个first_name进行排序编号 计数有多少排在它前面的	
		SELECT COUNT(*) 
    	FROM employees_sample AS e3 
     	WHERE e3.first_name <= e2.first_name) AS rowid 
     FROM employees_sample AS e2) AS e1
WHERE e1.rowid % 2 = 1;