[牛客数据库SQL实战] 21~30题及个人解答-白红宇

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

阅读量：4959 次

发布时间：2019-06-12

本文共 9655 字，大约阅读时间需要 32 分钟。

21. 查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

-- 通过连接员工表employees和薪水表salaries定位每一位员工SELECT ma.emp_no, (ma.salary-mi.salary) AS growthFROM (    -- 这里有个坑,题目没说是入职到*当前*(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 miON 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_timeSELECT de.dept_no, SUM(es.`time`) AS sum_timeFROM dept_emp AS de LEFT JOIN (    SELECT emp_no, COUNT(from_date) AS `time`     FROM salaries GROUP BY emp_no) AS esON de.emp_no=es.emp_noGROUP BY de.dept_no;-- 最后连接部门表department 显示部门名称dept_nameSELECT 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 dtON ds.dept_no=dt.dept_noORDER 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;

23. 对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

-- 在to_date='9999-01-01'的前提下-- 先从salaries选出一个薪水值,对所有薪水值大于该值的进行计数+1,将该计数称为排名-- +1是因为第一名没有比他大的,计数为0,但排名需要为1SELECT 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 soWHERE so.to_date='9999-01-01'ORDER BY `rank`, emp_no;

运行时间：20ms

占用内存：3320k

注意: 该代码虽然通过OJ系统的审核，但在实际环境上是十分缓慢的,是需要进行优化的SQL代码

实际环境是运行以下代码来求取结果的

-- 首先,找到薪水排行表SELECT DISTINCT s.salaryFROM salaries AS sWHERE 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_noSELECT 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 raLEFT JOIN (    SELECT emp_no, salary     FROM salaries AS s    WHERE s.to_date='9999-01-01') AS stON st.salary=ra.salaryORDER 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.salaryFROM salaries AS saJOIN dept_emp AS de ON de.emp_no = sa.emp_noLEFT JOIN dept_manager AS dm ON dm.emp_no = de.emp_noWHERE dm.emp_no IS NULLAND sa.to_date='9999-01-01';

运行时间：20ms

占用内存：3552k

-- 使用NOT IN语法-- 直译 不在manager表的emp_noSELECT de.dept_no, de.emp_no, s.salaryFROM salaries AS s INNER JOIN dept_emp AS deON s.emp_no=de.emp_noWHERE 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.salaryFROM salaries AS saJOIN dept_emp AS de ON de.emp_no = sa.emp_noLEFT JOIN dept_manager AS dm ON dm.emp_no = de.emp_noWHERE dm.emp_no IS NULLAND sa.to_date='9999-01-01';-- manager的薪水情况SELECT dm.dept_no, dm.emp_no, sa.salaryFROM salaries AS saJOIN dept_manager AS dm ON dm.emp_no = sa.emp_noAND sa.to_date='9999-01-01';-- 两表比较 -- 通过部门编号dept_no连接员工薪水表es和管理员薪水表msSELECT es.emp_no, ms.emp_no AS manager_no,     es.salary AS emp_salary, ms.salary AS manager_salaryFROM (    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 esINNER 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 msON es.dept_no=ms.dept_noWHERE 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 tWHERE 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_noGROUP 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的年份数据相减为1SELECT s1.emp_no, s1.from_date, (s1.salary-s2.salary) AS salary_growthFROM salaries AS s1, salaries AS s2WHERE 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_growthFROM (    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 sgWHERE sg.salary_growth > 5000;-- ORDER BY sg.salary_growth DESC; 过于缓慢-- 也可以直接将表达式放入where判断中SELECT s1.emp_no, s1.from_date, (s1.salary-s2.salary) AS salary_growthFROM salaries AS s1, salaries AS s2WHERE 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 riINNER 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 cnON 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.titleFROM film AS fLEFT JOIN film_category AS fc ON f.film_id=fc.film_idWHERE fc.film_id IS NULL;

运行时间：24ms

占用内存：3536k

注意:实际环境下测试得到为空,即film表中所有电影都被分类...

30. 使用子查询的方式找出属于Action分类的所有电影对应的title,description

-- 先通过category,film_category两表连接, 找出'Action'分类对应的film_idSELECT fc.film_idFROM film_category AS fc INNER JOIN category AS c ON fc.category_id=c.category_id AND c.name='Action';-- 通过上一查询得出的结果film_id 使用film表得到电影的title和descriptionSELECT title, descriptionFROM filmWHERE 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