牛客网数据库SQL实战解析(21-30题)

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

SELECT e.emp_no, (s1.salary-s2.salary) AS growth
FROM employees e
INNER JOIN salaries s1
ON e.emp_no=s1.emp_no AND s1.to_date='9999-01-01'
INNER JOIN salaries s2
ON e.emp_no=s2.emp_no AND e.hire_date=s2.from_date
ORDER BY growth 
;

解析：查出所有员工当前的薪水和入职时的薪水，求出涨幅，再关联emp_no，做升序排列。

第22题统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及次数sum

SELECT a.dept_no,
       a.dept_name,
       count(1) as sum
FROM(
    select * from  departments dp
    LEFT JOIN dept_emp de
    ON dp.dept_no=de.dept_no
) a
LEFT JOIN salaries s
on a.emp_no=s.emp_no
GROUP BY dept_no, dept_name
;

解析：两次left join 关联即可得到所需要的数据。

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

SELECT emp_no,
       salary,
       DENSE_RANK() OVER(ORDER BY salary DESC) AS rank
FROM salaries
WHERE to_date='9999-01-01'
;

解析：根据给出的输出示例，排序是连续的，用dense_rank()即可，如果排名非连续，那么就用rank()函数。

第24题获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'

SELECT de.dept_no, 
       s.emp_no, 
       s.salary 
FROM dept_emp AS de 
INNER JOIN salaries AS s 
ON s.emp_no = de.emp_no 
AND s.to_date = '9999-01-01'
WHERE de.emp_no NOT IN (
    SELECT emp_no 
    FROM dept_manager 
    WHERE to_date = '9999-01-01'
)
;

解析: 这道题可以不用上题目给的employees表，只要emp_no不在dept_manager表里面即可。

使用left join 时，on和where的区别：on是在生产临时表⑩的条件，而where是生成临时表后，对临时表加过滤条件。

25. 获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

SELECT de.emp_no
     , dm.emp_no AS manager_no
     , s1.salary
     , s2.salary AS manager_salary
FROM dept_emp de
    ,salaries s1
    ,dept_manager dm
    ,salaries s2
WHERE de.emp_no=s1.emp_no
  AND de.dept_no=dm.dept_no
  AND dm.emp_no=s2.emp_no
  AND s1.salary>s2.salary
  AND de.to_date='9999-01-01'
  AND dm.to_date='9999-01-01'
  AND s1.to_date='9999-01-01'
  AND s2.to_date='9999-01-01'
;

题解二：

SELECT sem.emp_no
     , sdm.emp_no AS manager_no
     , sem.salary AS emp_salary
     , sdm.salary AS manager_salary
FROM(
    SELECT s.emp_no
         , s.salary
         , de.dept_no
    FROM salaries s
    INNER JOIN dept_emp de
    ON s.emp_no=de.emp_no
    AND s.to_date='9999-01-01'
    ) sem,
    (SELECT s.emp_no
          , s.salary
          , dm.dept_no
     FROM salaries s
     INNER JOIN dept_manager dm
     ON s.emp_no=dm.emp_no
     AND s.to_date='9999-01-01'
     ) sdm
WHERE sem.dept_no=sdm.dept_no
AND sem.salary>sdm.salary
;

题解：逻辑很简单，找到员工和对应的薪资，起个别名就可以了。显然第一种方法更简洁，不过第二种方法更易读。

26. 汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count


SELECT de.dept_no, 
       dp.dept_name, 
       t.title, 
       COUNT(t.title) AS count
FROM titles t
INNER JOIN dept_emp de
ON de.emp_no=t.emp_no
AND de.to_date='9999-01-01'
AND t.to_date='9999-01-01'
INNER JOIN departments dp
ON de.dept_no=dp.dept_no
GROUP BY de.dept_no, dp.dept_name, t.title
;

解析：基本操作

27. 给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)

SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no 
AND salary_growth > 5000
AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1 
     OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )
ORDER BY salary_growth DESC
;

解析：1、假设s1是涨薪水前的表，s2是涨薪水后的表，因为每个员工涨薪水的时间不全固定，有可能一年涨两次，有可能两年涨一次，所以每年薪水的涨幅，应该理解为两条薪水记录的from_date相同或to_date相同。

2、找到s1与s2符合要求的记录后，用s2的薪水减去s1的薪水，用salary_growth表示，加上限定条件 s1.emp_no = s2.emp_no AND salary_growth > 5000，即同一员工每年涨幅超过5000的记录

28. 查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

SELECT c.name, 
       COUNT(fc.film_id) 
FROM(
    SELECT category_id, 
           COUNT(film_id) AS category_num 
    FROM film_category  
    GROUP BY category_id 
    HAVING count(film_id)>=5
) AS cc,
film AS f, 
film_category AS fc, 
category AS c
WHERE  f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id=cc.category_id
;

解析：重要的是灵活复用已有的表。

29. 使用join查询方式找出没有分类的电影id以及名称

SELECT f.film_id, f.title
FROM film f
LEFT JOIN film_category fc
ON f.film_id=fc.film_id
WHERE category_id is null
;

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

SELECT f.title, f.description 
FROM (
    SELECT fc.film_id
    FROM film_category fc
    LEFT JOIN category c
    ON fc.category_id=c.category_id
    WHERE c.name='Action'
) t
LEFT JOIN film f
ON t.film_id=f.film_id
;

题解二：

SELECT f.title,f.description 
FROM film AS f
WHERE f.film_id IN (
    SELECT fc.film_id 
    FROM film_category AS fc
    WHERE fc.category_id IN (
        SELECT c.category_id 
        FROM category AS c
        WHERE c.name = 'Action'
    )
);

解析：这倒题很简单，首先想到的就是题解1，用left join 做。但是题目要求的是用子查询实现，子查询也很简单，如题解二所示。