牛客mysql题解

2021-01-03 约 4066 字预计阅读 9 分钟

链接： https://www.nowcoder.com/ta/sql

SQL1

SELECT * from employees Order BY hire_date DESC LIMIT 1;

SQL2

-- OFFSET要在limit后面，这样可以选出倒数第三个
SELECT * from employees Order BY hire_date DESC LIMIT 1 OFFSET 2;

SQL3

SELECT salaries.*,dept_manager.dept_no FROM salaries LEFT JOIN 
dept_manager ON salaries.emp_no = dept_manager.emp_no 
WHERE salaries.to_date='9999-01-01' AND dept_manager.to_date = 
'9999-01-01' ORDER BY salaries.emp_no asc;

SQL4

SELECT employees.last_name,employees.first_name,dept_emp.dept_no FROM 
dept_emp LEFT JOIN employees ON dept_emp.emp_no = employees.emp_no;

SQL5

SELECT employees.last_name,employees.first_name,dept_emp.dept_no FROM employees LEFT JOIN dept_emp 
ON employees.emp_no = dept_emp.emp_no;

SQL6

SELECT employees.emp_no,salaries.salary FROM employees INNER JOIN salaries ON employees.emp_no = 
salaries.emp_no AND salaries.from_date = employees.hire_date ORDER BY employees.emp_no DESC;

前面的几个where是在链接好了之后再进行排序，on是在生成临时表的时候就排序，两个还是不太一样的。

SQL7

SELECT emp_no,count(emp_no) AS t FROM salaries GROUP BY emp_no HAVING t >
15;

思路就是先GROUP分组，然后再看看分好的组里面哪个大于15

SQL8

SELECT DISTINCT salary FROM salaries WHERE salaries.to_date = '9999-01-01' ORDER BY salaries.salary DESC;

这一题比较简单

SQL9

SELECT dept_manager.dept_no,dept_manager.emp_no,salaries.salary FROM dept_manager LEFT JOIN salaries 
on dept_manager.emp_no = salaries.emp_no WHERE dept_manager.to_date='9999-01-01' AND 
salaries.to_date='9999-01-01' ORDER BY dept_manager.dept_no ASC;

SQL10

SELECT employees.emp_no FROM employees LEFT JOIN dept_manager ON employees.emp_no = dept_manager.emp_no WHERE dept_manager.dept_no IS NULL;

这道题目有点坑，dept_no是主管的id，有这个的才是主管，

SQL11

SELECT dept_emp.emp_no,dept_manager.emp_no FROM dept_manager LEFT JOIN dept_emp 
ON dept_manager.dept_no = dept_emp.dept_no WHERE dept_manager.emp_no != dept_emp.emp_no 
AND dept_manager.to_date='9999-01-01';

SQL12

SELECT ss.dept_no,d.emp_no,ss.maxSalary FROM (
SELECT d.dept_no,MAX(s.salary) AS maxSalary FROM salaries AS s,dept_emp AS d
    WHERE s.emp_no = d.emp_no
    AND s.to_date='9999-01-01' 
    AND d.to_date = '9999-01-01'
    GROUP BY d.dept_no
) AS ss ,dept_emp AS d,salaries AS s
WHERE ss.maxSalary = s.salary
AND ss.dept_no = d.dept_no 	 -- 查询出来的dept_no要和dept_no一致
AND d.emp_no = s.emp_no 
AND d.to_date = '9999-01-01' -- 题目限定条件
AND s.to_date = '9999-01-01' -- 题目限定条件
ORDER BY d.dept_no ASC;

这一题比较复杂，思路就是先在salaries表和dept_emp表中按部门编号（dept_no）分组，然后取salary最大的，这样就可以得到单个员工最大的工资了。

这样得到的结果是:

+---------+-----------+
| dept_no | maxSalary |
+---------+-----------+
| d001    |     88958 |
+---------+-----------+

然后这个时候再把这个得到的表和dept_emp,salaries一起查找查找的条件就是

SQL13

SELECT title,COUNT(title) T
FROM titles
GROUP BY title HAVING T >= 2;

SQL14

SELECT title, COUNT(DISTINCT emp_no) t
FROM titles
GROUP BY title
HAVING t>=2;

思路就是在统计的时候就把emp_no去重

SQL15

SELECT * FROM employees
WHERE last_name <> 'Mary'
AND emp_no%2 = 1
ORDER BY hire_date DESC;

筛选字符用<>，表示last_name != Mary

SQL16

SELECT t.title,AVG(s.salary) FROM salaries s,titles t
WHERE s.to_date = '9999-01-01'
    AND t.to_date = '9999-01-01' 
    AND s.emp_no = t.emp_no
GROUP BY t.title;

SQL17

SELECT emp_no,salary FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY salary DESC LIMIT 1 OFFSET 1;

这一题比较简单，注意offset放在limit后面就行了

SQL18

SELECT e.emp_no,s.salary,e.last_name,e.first_name FROM employees e 
INNER JOIN salaries s		-- inner连接
ON s.emp_no = e.emp_no
AND s.to_date = '9999-01-01'
WHERE s.salary = (
  			SELECT MAX(s2.salary) FROM salaries s2 -- 再从比最大的小的记录中找到最大的，也就是可以找到第二大的了
      	WHERE s2.salary < (SELECT MAX(salary) FROM salaries --先找到最大的
                           WHERE to_date = '9999-01-01')
      AND s2.to_date = '9999-01-01');

SQL19

SELECT e.last_name,e.first_name,dd.dept_name FROM
employees e LEFT JOIN (
    SELECT dept_name,emp_no FROM departments d,dept_emp de WHERE d.dept_no = de.dept_no
) AS dd
ON dd.emp_no = e.emp_no

SQL20

SELECT (MAX(salary) - MIN(salary)) AS growth FROM salaries
WHERE emp_no = 10001;

这题比较简单没什么好说的

SQL21

SELECT a.emp_no,(b.salary - c.salary) AS growth
FROM employees a 
    INNER JOIN salaries b 
    ON a.emp_no = b.emp_no AND b.to_date = '9999-01-01'
    INNER JOIN salaries c
    ON a.emp_no = c.emp_no AND a.hire_date = c.from_date
    ORDER BY growth ASC;

本题一共选了三个临时表，第一个临时表是employee和salaries的交集，这次取交集把两个都有的emp_no和没有离职的给获取到了，

第二个临时表是employee和salaries的交集，这里面仍然要求emp_no为employee和salaries共有的，并且要求被入职时间=薪水开始记录的时间，因为题目要求查找所有员工入职以来薪水的涨幅情况，所以一定要把入职时间确定下来

SQL22

SELECT d.dept_no, d.dept_name, COUNT(*) AS sum
FROM departments AS d, dept_emp AS de, salaries AS s
WHERE d.dept_no=de.dept_no
AND de.emp_no=s.emp_no
GROUP BY d.dept_no;

这个题库有个很奇怪的地方，22题我用mysql每次提交都是16%通过率，欢乐sqlite就变成了100%通过率

这个思路就是先WHERE查询，找到departments

SQL23

SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
FROM salaries AS s1, salaries AS s2
WHERE s1.to_date = '9999-01-01'  
AND s2.to_date = '9999-01-01' 
AND s1.salary <= s2.salary
GROUP BY s1.emp_no
ORDER BY s1.salary DESC, s1.emp_no ASC

解决思路是用两个表来进行排序

SQL24

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

先找到所有非manager员工emp_no,再内连接工资表和部门

SQL25

SELECT de.emp_no,-- 员工的
       dm.emp_no as manager_no,-- manager的
       s1.salary as emp_salary,-- 员工薪水
       s2.salary as manager_salary -- manager薪水
FROM dept_emp de,dept_manager dm,salaries s1,salaries s2
WHERE de.dept_no=dm.dept_no
AND de.emp_no=s1.emp_no
AND dm.emp_no=s2.emp_no
AND s1.to_date='9999-01-01'
AND s2.to_date='9999-01-01'
AND s1.salary>s2.salary

s1中是所有员工的，s2是manager的，只要所有员工有大于manager表中的，那么就代表这是比manager大的

SQL26

SELECT d.dept_no, d.dept_name, t.title, COUNT(t.title)
FROM departments d JOIN dept_emp de ON d.dept_no = de.dept_no
JOIN titles t ON de.emp_no = t.emp_no
WHERE t.to_date = '9999-01-01'
AND de.to_date = '9999-01-01'
GROUP BY d.dept_no, t.title
ORDER BY d.dept_no

这里就直接join了，因为题目是要求的各个部门所有的员工的，然后根据部门编号和title分组，这样会出来所有的部门编号，然后就可以统计对应的title了

SQL27

SELECT s1.emp_no,s2.from_date,(s2.salary-s1.salary) salary_growth
FROM salaries s1
JOIN salaries s2 ON s1.emp_no = s2.emp_no 
	AND s1.to_date = s2.from_date
WHERE s2.salary-s1.salary > 5000
ORDER BY salary_growth DESC

SQL28

SELECT c.name,COUNT(f.film_id)
FROM film f JOIN film_category fc ON f.film_id=fc.film_id
JOIN category c ON fc.category_id=c.category_id
WHERE f.description LIKE '%robot%'
  AND c.category_id IN (SELECT category_id  FROM film_category  GROUP BY category_id 															HAVING COUNT(film_id)>=5)
GROUP BY c.name

题目要求要分类，所以要先按照name分类，然后HAVING 把小于5的给筛选掉。

从电影分类表和电影总表中，根据film_id 联合起来，然后再和电影分类名称表根据电影分类的id联合起来，这样信息就全了，

然后筛选信息的时候就看看description是不是包含robot

并且category_id 也要在film_category的表中有大于5的电影

SQL29

SELECT f.film_id, f.title
FROM film AS f
LEFT JOIN film_category AS fc
ON f.film_id=fc.film_id
WHERE category_id IS NULL;

不管film_category表中有没有这个电影的记录，film表必须要有，所以用左连接

查询的结果如下所示

+---------+------------------+-------------+
| film_id | title            | category_id |
+---------+------------------+-------------+
|       3 | ADAPTATION HOLES |        NULL |
+---------+------------------+-------------+

SQL30

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

film_category，film两个表先根据film_id进行连接，然后再和category进行连接，这样可以得到完整的包含title和description的联合表，其中必须要保证film和category的film_id和category_id在film_category有记录（连接的条件也只能是这样）

这样得到了一个大的信息表，然后再用where筛选就可以了

目录