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);
- 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
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;
运行时间：17ms
占用内存：3552k
-- 在where 直接判断计数是否为奇数
SELECT eo.first_name
FROM employees_sample AS eo
WHERE (
SELECT COUNT(*)
FROM employees_sample AS ei
WHERE ei.first_name <= eo.first_name
) % 2 = 1;
- 使用COUNT()函数来做排名
完整的个人练习代码
我的练习SQL代码已经上传至Github： https://github.com/slowbirdoflsh/newcode-sql-practice
仅供参考~~~