一些自己整理的sql语句例题

 1.请查找employees里最晚入职(hire_date)员工的所有信息（select top 1 * from 是SQL Server的查询语句）

//正确答案
select * from employees 
where hire_date = (
                    select max(hire_date) from employees
                  )

• 可能会写成的错误答案想法
  • ORDER BY 根据指定的列对结果集进行排序，默认按照升序，降序 ORDER BY DESC
  • LIMIT(m, n) 从第 m + 1 行开始取 n 条记录
• 最晚员工自然是 hire_data，最晚可以用排序 ORDER BY DESC 降序来得到，然后是获取第一条记录，这样理论上是有 bug 的，因为 hire_data 可能有多个相同的记录

//可能会写成的错误答案
SELECT * FROM employees 
order by hire_date desc limit 0,1

---

2.查找入职员工时间(hire_date)排名倒数第三的员工所有信息

//正解1
select * from employees
where hire_date =
(select distinct hire_date from employees 
order by hire_date desc limit 2,1)


//正解2
select * from employees e1
where 2=
(select count(*) from employees e2 where e1.hire_date < e2.hire_date)

---

3.有一个全部员工的薪水表salaries(emp_no,salary)，一个各个部门的领导表dept_manager(dept_no,emp_no)。请查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列

select s.*,m.dept_no
from salaries s right join dept_manager m 
ON s.emp_no = m.emp_no
order by s.emp_no 

---

4.有一个员工表employees(emp_no,name),有一个部门领导表dept_manager(emp_no,dept_no,name)。请找出所有非部门领导的员工emp_no

//方法一：使用NOT IN选出在employees但不在dept_manager中的emp_no记录
SELECT emp_no FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)

//方法二：先使用LEFT JOIN连接两张表，再从此表中选出dept_no值为NULL对应的emp_no记录
SELECT emp_no FROM (SELECT * FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no)
WHERE dept_no IS NULL

//方法三：方法二的简版，使用单层SELECT语句即可
SELECT employees.emp_no FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no
WHERE dept_no IS NULL

---

5.有一个员工表dept_emp(emp_no,dept_no),有一个薪水表salaries(emp_no,salary),获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列。

       此题常见漏洞：

        1.emp_no直接和group by dept_no一起使用，拿到了最大salary但是存在emp_no取值其实与salary不匹配的问题；

        2. 先使用group by获得最高salary，再去用最高salary匹配两表返回dept_no,emp_no信息，这存在A部门的最高薪水，等于B部门非最高薪水时，B部门的非最高薪水也会被显示出来。

//解法一：（如果同部门有多条同等最大salary，一起显示出来）
select r.dept_no,ss.emp_no,r.maxSalary from (
select d.dept_no,max(s.salary)as maxSalary from dept_emp d,salaries s
where d.emp_no=s.emp_no
group by d.dept_no
)as r,salaries ss,dept_emp dd
where r.maxSalary=ss.salary
and r.dept_no=dd.dept_no
and dd.emp_no=ss.emp_no
order by r.dept_no asc
//解法二：（如果同部门有多条同等最大salary，仅显示一条）
select r.dept_no,r.emp_no,max(r.salary) from (
select d.dept_no,d.emp_no,s.salary from dept_emp d,salaries s
where d.emp_no=s.emp_no
order by s.salary desc
)as r
group by r.dept_no
order by r.dept_no asc

注明两点：

1.题目忘记写一条信息，按照部门编号排序

2.解法二利用了GROUP BY 默认取非聚合数据的第一条记录，所以先排好序，拿到的emp_no第一条信息，也是与最大salary匹配的

3.解法一中使用多表取值，where筛选条件和内连接，on筛选条件，效果一致，可以替换。效率根据不同表的结构，数据结构而定。

---

6.emp_no为奇数，且last_name不为Mary的员工信息

select * from employees
where emp_no % 2 = 1
and last_name != 'Mary'
order by hire_date desc