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