SQL操作总结

本篇主要是针对博主在牛客上做的一些比较有意思的sql题进行的总结：

题1：

获取所有非manager的员工emp_no
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

方法一：使用NOT IN选出在employees但不在dept_manager中的emp_no记录

1 2	SELECT emp_no FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)

方法二：使用单层SELECT语句+left join即可

1 2 3

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

题二：

获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解法：

select dept_emp.dept_no,dept_emp.emp_no,max(salaries.salary)
from dept_emp
inner join salaries on dept_emp.emp_no = salaries.emp_no
where dept_emp.to_date = "9999-01-01" and salaries.to_date="9999-01-01"
group by dept_emp.dept_no

题三：

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

解法：

此题应注意以下三点：

1、先用GROUP BY title将表格以title分组，再用COUNT(DISTINCT emp_no)可以统计同一title值且不包含重复emp_no值的记录条数

2、根据题意，输出每个title的个数为t，故用AS语句将COUNT(DISTINCT emp_no)的值转换为t

3、由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>=2的条件

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

注意：先group by title，再having  t > 2

题四：

解法：

三点需要注意：

1、员工号为奇数，则emp_no取余应为1

2、last_name不为Mary，用‘！=’表示

3.根据hire_date逆序排列，用desc

题5：

解法:

    注意当前是用"9999-01-01"表示的

 

题6：

方法一：在有多人拿最高工资的情况下，limit 1,1 也不能保证取到的就是第二大的工资

方法二：这种更值得推荐！！！

 

题7：

解答：

本题做法很多，主要思想为多层SELECT嵌套与MAX()函数结合

1、先利用MAX()函数找出salaries中当前薪水最高者，即SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01'

2、再利用INNER JOIN连接employees与salaries表，限定条件为【同一员工】e.emp_no = s.emp_no、【当前】s.to_date = '9999-01-01'与【非薪水最高】s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')

3、在以上限制条件下找薪水最高者，即为所有员工薪水的次高者

方法一：利用not in 来判断不是最高

1 2 3 4 5

SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name  FROM employees AS e INNER JOIN salaries AS s  ON e.emp_no = s.emp_no WHERE s.to_date = '9999-01-01' AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')

方法二：我的思路：先求出最大，然后利用< 来判断

方法三：

select e.emp_no, salary, last_name, first_name
from employees as e inner join salaries as s
where e.emp_no = s.emp_no and s.to_date='9999-01-01'
and salary = 
(select max(salary) from salaries where salary < (select max(salary) from salaries)) 

 

题8：

查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

方法：

       由于三张表，dept_emp是中间表，所以需要两次left join 

 

题9

解法：

    

如果题目换成最后一次工资和第一次工资：

 

题10

解法：

大致思路，我们直接连出来一张表就好了

(id, 来的时候的工资，最后的工资)

这张表第二个字段和第三个字段都涉及到了salary

所以一定会级联两次表salaries
也就是employees join salaries join salaries之后用on把条件筛选一个就可以了。