牛客mysql题解
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筛选就可以了