1.查找最晚入职员工的所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天
select *
from employees
where emp_no=10008;
2.查找入职员工时间排名倒数第三的员工所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天
select *
from employees
where hire_date = (select distinct hire_date
from employees
order by hire_date desc
limit 2,1)
- 用limit语句返回第三行数据
3.查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no
(注:请以salaries表为主表进行查询,输出结果以salaries.emp_no升序排序,并且请注意输出结果,dept_no列是最后一列)
select s.*,d.dept_no
from salaries s join dept_manager d
on s.emp_no=d.emp_no
where s.to_date='9999-01-01'
and d.to_date='9999-01-01'
order by s.emp_no
4.查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)
select e.last_name,e.first_name,d.dept_no
from employees as e
inner join dept_emp as d
on e.emp_no=d.emp_no;
- 用inner join去除dept_no为NULL的行
5.查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)
select e.last_name,e.first_name,d.dept_no
from employees as e
left join dept_emp as d
on e.emp_no=d.emp_no;
- 用left join保留左表(含对应dept_no空值的employees表)的所有行
6.查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序(请注意,一个员工可能有多次涨薪的情况)
- 员工有多次涨薪可能,代表salaries表内一个emp_no对应的salary有可能不止一个数据。salaries表有两个主键,两个主键都不一样才可对应一条单独的数据。当e.emp_no=s.emp_no时,可能有若干个s表的数据对应到e表。入职时候的薪水情况,如果入职即记录薪水,那入职时间和第一次薪水时间应该相同,则采用e.hire_date=s.from_date。但如果入职日期不记录薪水,而是等发薪水才记录,且只有涨薪情况时,考虑可以通过选择最小薪水min(s.salary)。如果单人有多条同样薪水大小的记录,可以用distinct来去除相同值。
select emp_no,salary
from (select emp_no,salary,row_number() over (partition by emp_no
order by from_date)as a
from salaries)
where a=1
ORDER BY emp_no DESC
- 用row_number() over来给每个人的薪水发放时间进行排序
select e.emp_no,salary
from employees e join salaries s
on e.emp_no=s.emp_no
and e.hire_date=s.from_date
ORDER BY e.emp_no DESC
- 用join语句联结表格实现令条件相等的目的。但总觉得有可能hire_date会有与from_date对应不上的情况(入职时记录了入职时间,但等到第一次薪水发放才记录from_date)。如果是这样的话需要用第四种方法。
select emp_no,salary
from salaries
group by emp_no
having salary=min(salary)
ORDER BY emp_no DESC
- 薪水只涨不跌的情况
select emp_no,salary
from salaries
group by emp_no
having from_date=min(from_date)
ORDER BY emp_no DESC
- 薪水不可能只涨,所以选择最小的发放时间(即入职时间)。这个代码可能是最短,最切合条件的。但网上有说having比较耗资源,也有说这个having的用法是错误的。暂且记录下,后期学习。
- “声明下这个答案是错的,不过思路确实很好。首先select中没有使用aggregate函数,所以groupby之后,得到的salary值是不确定的,默认可能是检索过程中碰到的第一条数据。其次having后需要跟一个判断条件,返回boolean值。根据非false即true的判定,返回的min(from_date)虽然是个date但被认为是true,即条件衡为true,having筛选失去意义。因为测试数据不完善的关系,出现了测试全通过的情况,实际上这个答案是错误的。不过思路没问题,可能不需要employees表也可以实现需求。”——Litec
9.获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况,给出dept_no, emp_no以及salary(请注意,同一个人可能有多条薪水情况记录)
select dept_no,d.emp_no,salary
from salaries s inner join dept_manager d
on d.emp_no=s.emp_no
and d.to_date='9999-01-01'
and s.to_date='9999-01-01';
- 这一题如果用where而非and来限制条件的话会报错,应该是网站的锅而非代码有误
10.获取所有非manager的员工emp_no
select e.emp_no
from employees as e left join dept_manager as d
on e.emp_no=d.emp_no
where d.emp_no is null;
- 用联结表格实现检索不是manager的员工
SELECT emp_no
FROM employees
WHERE emp_no NOT IN (SELECT emp_no
FROM dept_manager)
- 用not in来实现选取不是manager的员工