[牛客数据库SQL实战] 21~30题及个人解答
21. 查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
-- 通过连接员工表employees和薪水表salaries定位每一位员工
SELECT ma.emp_no, (ma.salary-mi.salary) AS growth
FROM (
-- 这里有个坑,题目没说是入职到*当前*(to_date='9999-01-01')的薪水情况
-- 我之前是使用分组group by找到最大to_date来计算的,但没法通过
SELECT s.emp_no, s.salary, s.to_date
FROM employees AS e
LEFT JOIN salaries AS s
ON s.emp_no=e.emp_no AND s.to_date='9999-01-01'
) AS ma INNER JOIN (
SELECT s.emp_no, s.salary
FROM employees AS e
LEFT JOIN salaries AS s
ON s.emp_no=e.emp_no AND s.from_date=e.hire_date
) AS mi
ON ma.emp_no=mi.emp_no ORDER BY growth;
运行时间：25ms
占用内存：3432k
通过员工表employees的emp_no来定位每一位员工
通过员工表employees的hire_date来定位入职时间
22. 统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum
-- 首先,找到每位员工的涨幅次数 即to_date/from_date的个数
SELECT emp_no, COUNT(from_date) AS `time` FROM salaries GROUP BY emp_no;
-- 再使用部门员工表dept_emp连接员工涨幅表 得到部门编号和每个部门员工涨幅值sum_time
SELECT de.dept_no, SUM(es.`time`) AS sum_time
FROM dept_emp AS de
LEFT JOIN (
SELECT emp_no, COUNT(from_date) AS `time`
FROM salaries GROUP BY emp_no) AS es
ON de.emp_no=es.emp_no
GROUP BY de.dept_no;
-- 最后连接部门表department 显示部门名称dept_name
SELECT dt.dept_no, ds.dept_name, dt.sum_time AS `sum`
FROM departments AS ds
INNER JOIN (
SELECT de.dept_no, SUM(es.`time`) AS sum_time
FROM dept_emp AS de
LEFT JOIN (
SELECT emp_no, COUNT(from_date) AS `time`
FROM salaries GROUP BY emp_no) AS es
ON de.emp_no=es.emp_no
GROUP BY de.dept_no) AS dt
ON ds.dept_no=dt.dept_no
ORDER BY dt.dept_no;
运行时间：27ms
占用内存：4824k
- 注意:这题也是有坑的... 本质上是:统计各个部门的工资记录数
1、涨幅为0算不算涨幅？ salary不变
2、以前的(已离职)员工算不算？ to_date < '9999-01-01'
3、若员工是刚入职的只有一条salaries薪水记录，这个人涨幅数是0还是1？ COUNT(from_date)==1
答案也都是忽视这三个问题才成立的
- 符合实际需求的一份解答: (1m 14s)
select d.dept_no, d.dept_name,
(select
sum((select
sum((select
case
# 记录数为0说明是第一条记录（原来的答案里有这一条，测试后发现加上去答案和预期不符）
# when count(*) = 0 then 0
# 最近一次工资变化比当前工资低判定为涨工资
when
s0.salary < s.salary then 1
# 其他情况判定为不是涨工资
else 0
end
# 查询最近一次工资变化情况
from salaries s0
where s0.emp_no = s.emp_no and s0.to_date < s.to_date
order by s0.to_date desc limit 1))
# 查询出每个成员的每次工资变化情况
from salaries s where s.emp_no = de.emp_no))
# 查询出部门中的每个成员
from dept_emp de where de.dept_no = d.dept_no) as `sum`
from departments d;
MySql 中 case when then else end 的用法
MySQL官方文档：CASE语法
23. 对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
-- 在to_date='9999-01-01'的前提下
-- 先从salaries选出一个薪水值,对所有薪水值大于该值的进行计数+1,将该计数称为排名
-- +1是因为第一名没有比他大的,计数为0,但排名需要为1
SELECT so.emp_no, so.salary,
(SELECT COUNT(DISTINCT si.salary)
FROM salaries AS si
WHERE si.to_date='9999-01-01' AND si.salary > so.salary)+1 AS `rank`
FROM salaries AS so
WHERE so.to_date='9999-01-01'
ORDER BY `rank`, emp_no;
运行时间：20ms
占用内存：3320k
-
注意: 该代码虽然通过OJ系统的审核，但在实际环境上是十分缓慢的,是需要进行优化的SQL代码
-
实际环境是运行以下代码来求取结果的
-- 首先,找到薪水排行表
SELECT DISTINCT s.salary
FROM salaries AS s
WHERE s.to_date='9999-01-01'
ORDER BY s.salary DESC;
-- 其次,给薪水排行表标号
SELECT sa.salary, (@id:=@id+1) AS `rank`
FROM (SELECT @id:=0) AS it,(
SELECT DISTINCT s.salary
FROM salaries AS s
WHERE s.to_date='9999-01-01'
ORDER BY s.salary DESC) AS sa;
-- 通过薪水排行去获取员工编号emp_no
SELECT st.emp_no, ra.salary, ra.`rank`
FROM (
SELECT sa.salary, (@id:=@id+1) AS `rank`
FROM (SELECT @id:=0) AS it,(
SELECT DISTINCT s.salary
FROM salaries AS s
WHERE s.to_date='9999-01-01'
ORDER BY s.salary DESC) AS sa ) AS ra
LEFT JOIN (
SELECT emp_no, salary
FROM salaries AS s
WHERE s.to_date='9999-01-01') AS st
ON st.salary=ra.salary
ORDER BY ra.salary DESC, st.emp_no ASC;
- 注意:上一方法无法通过审核,可能是不支持@id的自增操作,但是4s结果就出来了(虽然不太清楚正确与否).
24. 获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary
当前表示to_date='9999-01-01'
-- 使用LEFT JOIN语法
-- 右表为NULL即表示其记录为左边独有
SELECT de.dept_no, de.emp_no, sa.salary
FROM salaries AS sa
JOIN dept_emp AS de ON de.emp_no = sa.emp_no
LEFT JOIN dept_manager AS dm ON dm.emp_no = de.emp_no
WHERE dm.emp_no IS NULL
AND sa.to_date='9999-01-01';
运行时间：20ms
占用内存：3552k
-- 使用NOT IN语法
-- 直译 不在manager表的emp_no
SELECT de.dept_no, de.emp_no, s.salary
FROM salaries AS s
INNER JOIN dept_emp AS de
ON s.emp_no=de.emp_no
WHERE s.to_date='9999-01-01' AND de.to_date='9999-01-01'
AND de.emp_no NOT IN (
SELECT emp_no FROM dept_manager);
运行时间：21ms
占用内存：3300k
25. 获取员工其当前的薪水比其manager当前薪水还高的相关信息
当前表示to_date='9999-01-01',
-- 员工薪水情况
SELECT de.dept_no, de.emp_no, sa.salary
FROM salaries AS sa
JOIN dept_emp AS de ON de.emp_no = sa.emp_no
LEFT JOIN dept_manager AS dm ON dm.emp_no = de.emp_no
WHERE dm.emp_no IS NULL
AND sa.to_date='9999-01-01';
-- manager的薪水情况
SELECT dm.dept_no, dm.emp_no, sa.salary
FROM salaries AS sa
JOIN dept_manager AS dm ON dm.emp_no = sa.emp_no
AND sa.to_date='9999-01-01';
-- 两表比较
-- 通过部门编号dept_no连接员工薪水表es和管理员薪水表ms
SELECT es.emp_no, ms.emp_no AS manager_no,
es.salary AS emp_salary, ms.salary AS manager_salary
FROM (
SELECT de.dept_no, de.emp_no, sa.salary
FROM salaries AS sa
JOIN dept_emp AS de ON de.emp_no = sa.emp_no
LEFT JOIN dept_manager AS dm ON dm.emp_no = de.emp_no
WHERE dm.emp_no IS NULL
AND sa.to_date='9999-01-01') AS es
INNER JOIN(
SELECT dm.dept_no, dm.emp_no, sa.salary
FROM salaries AS sa
JOIN dept_manager AS dm ON dm.emp_no = sa.emp_no
AND sa.to_date='9999-01-01' ) AS ms
ON es.dept_no=ms.dept_no
WHERE es.salary>ms.salary;
运行时间：20ms
占用内存：3452k
26. 汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
-- 三表连接,然后通过dept_no部门编号和title职称分组
SELECT de.dept_no,ds.dept_name, t.title, COUNT(t.emp_no) AS `count`
FROM departments AS ds, dept_emp AS de, titles AS t
WHERE de.to_date='9999-01-01' AND t.to_date='9999-01-01'
AND ds.dept_no=de.dept_no AND de.emp_no=t.emp_no
GROUP BY de.dept_no, t.title;
运行时间：23ms
占用内存：3320k
- 注意: GROUP BY对多个字段进行分组
GROUP BY X,Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。
如果将需要分组的字段都进行排序可能会容易理解一些
mysql> SELECT de.dept_no, t.title, de.emp_no
-> FROM dept_emp AS de
-> JOIN titles AS t
-> ON de.emp_no=t.emp_no
-> ORDER BY de.dept_no, t.title;
| dept_no | title | emp_no |
|---|---|---|
| d001 | Manager | 110039 |
| d001 | Manager | 110022 |
| d001 | Senior Staff | 10017 |
| d001 | Senior Staff | 18848 |
| d001 | Senior Staff | 15565 |
| d001 | Senior Staff | 10058 |
当以dept_no, title同时分组时, 将(dept_no, title)作为分组条件
27. 给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)
-- 通过strftime函数获取年份, 相差一年即from_date/to_date的年份数据相减为1
SELECT s1.emp_no, s1.from_date, (s1.salary-s2.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no=s2.emp_no
AND (s1.salary-s2.salary) > 5000
AND ((strftime('%Y',s1.from_date) - strftime('%Y',s2.from_date) = 1)
OR (strftime('%Y',s1.to_date) - strftime('%Y',s2.to_date) = 1))
ORDER BY salary_growth DESC;
运行时间：20ms
占用内存：3432k
- 注意: 该题的时间定位其实是有问题的, 按这种求法'1999-12-31'与'2000-00-00'也是相差一年
-- 在实际环境下, MySQL使用YEAR()函数获取年份数据
-- 而且基本上可以说无法排序(数据量过大, 可能需要索引)
-- 可以直接将薪水差值表作为临时表 过滤抽取数据
SELECT sg.emp_no, sg.from_date, sg.salary_growth
FROM (
SELECT s1.emp_no, s1.from_date, (s1.salary-s2.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no=s2.emp_no
AND (YEAR(s1.from_date) - YEAR(s2.from_date) = 1)
OR (YEAR(s1.to_date) - YEAR(s2.to_date) = 1)) AS sg
WHERE sg.salary_growth > 5000;
-- ORDER BY sg.salary_growth DESC; 过于缓慢
-- 也可以直接将表达式放入where判断中
SELECT s1.emp_no, s1.from_date, (s1.salary-s2.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no=s2.emp_no
AND (s1.salary-s2.salary) > 5000
AND ((YEAR(s1.from_date) - YEAR(s2.from_date) = 1)
OR (YEAR(s1.to_date) - YEAR(s2.to_date) = 1));
28. 查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部
SELECT cn.name, ri.`count`
FROM (
-- 先找出描述信息包含'robot'的电影分类 **加计数!!!**
SELECT fc.category_id, COUNT(fc.category_id) AS `count`
FROM film AS f
INNER JOIN film_category AS fc
ON f.film_id = fc.film_id
WHERE f.description LIKE '%robot%'
GROUP BY fc.category_id ) AS ri
INNER JOIN (
-- 再找出分类电影数目超过5部的电影分类
SELECT c.name, fc.category_id, COUNT(fc.category_id) AS c_num
FROM film_category AS fc
INNER JOIN category AS c
ON c.category_id=fc.category_id
GROUP BY fc.category_id
HAVING c_num >= 5) AS cn
ON cn.category_id = ri.category_id;
运行时间：18ms
占用内存：3644k
- 注意:需要理清题目意思
本题需要有两个条件,
1、电影描述信息包含'robot'的电影,根据分类分组获得各自(包含'robot')的计数
2、电影分类分组包含电影计数大于等于5
此题使用的是MySQL的示例数据库Sakila
29. 使用join查询方式找出没有分类的电影id以及名称
-- 没有分类,就说明film_category电影分类表中没有出现
-- film表有 而film_category表没有
SELECT f.film_id, f.title
FROM film AS f
LEFT JOIN film_category AS fc
ON f.film_id=fc.film_id
WHERE fc.film_id IS NULL;
运行时间：24ms
占用内存：3536k
- 注意:实际环境下测试得到为空,即film表中所有电影都被分类...
【mySQL】left join、right join和join的区别
30. 使用子查询的方式找出属于Action分类的所有电影对应的title,description
-- 先通过category,film_category两表连接, 找出'Action'分类对应的film_id
SELECT fc.film_id
FROM film_category AS fc
INNER JOIN category AS c
ON fc.category_id=c.category_id AND c.name='Action';
-- 通过上一查询得出的结果film_id 使用film表得到电影的title和description
SELECT title, description
FROM film
WHERE film_id IN (
SELECT fc.film_id
FROM film_category AS fc
INNER JOIN category AS c
ON fc.category_id=c.category_id AND c.name='Action');
运行时间：24ms
占用内存：3548k
-- 如果只全程使用子查询
SELECT title, description
FROM film
WHERE film_id IN (
SELECT film_id
FROM film_category
WHERE category_id IN (
SELECT category_id
FROM category
WHERE name='Action'
)
);
运行时间：18ms
占用内存：3676k
完整的个人练习代码
我的练习SQL代码已经上传至Github： https://github.com/slowbirdoflsh/newcode-sql-practice
仅供参考~~~
