SQL实战03

21 查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!='9999-01-01'，这样的数据不显示在查找结果里面）

select a.emp_no,(a.salary-b.salary) growth FROM (select emp_no,salary from salaries WHERE to_date='9999-01-01') a
join (select s.salary,s.emp_no from employees e join salaries s on e.hire_date=s.from_date and e.emp_no=s.emp_no) b 
on a.emp_no=b.emp_no ORDER BY growth;
+--------+--------+
| emp_no | growth |
+--------+--------+
|  10004 |   3359 |
|  10001 |  10041 |
+--------+--------+
2 rows in set (0.00 sec)

注释：定义a和b 

a 是当前工资 current_salary  b是初始工资 start_salary

a.salary-b.salary 定义为growth

排序关键词为growth

a 取值：to_date='9999-01-01'时的工资和emp_no

b取值：需要内联salaries和employees两张表，on hire_date和from_date一致，另外emp_no一致

a和b也需要关键字连起来emp_no

排序默认为升序

22 统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum

select de.dept_no,de.dept_name,count(*) sum 
FROM (departments de inner join dept_emp d ON de.dept_no=d.dept_no INNER join salaries s ON d.emp_no=s.emp_no) 
GROUP BY d.dept_no;
+---------+-------------+-----+
| dept_no | dept_name   | sum |
+---------+-------------+-----+
| d001    | Marketing   |   2 |
| d004    | Production  |   5 |
| d002    | Finance     |   1 |
| d005    | Development |   1 |
+---------+-------------+-----+
4 rows in set (0.00 sec)

注释：三表联合查询，两次内联

三表：departments de        dept_emp d        salaries   s

departments和dept_emp 内联字段为dept_no员工部门编号

dept_no和salaries内联字段为emp_no为员工编号

部门的salaries表有多少记录，只需要以dept_no为group by字段

23 对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

select emp_no,salary,dense_rank() over w as 'rank'
from salaries where to_date='9999-01-01' 
WINDOW w as (order by salary desc);
+--------+--------+------+
| emp_no | salary | rank |
+--------+--------+------+
|  10001 |  98999 |    1 |
|  10002 |  88070 |    2 |
|  10007 |  88070 |    2 |
|  10004 |  74057 |    3 |
|  10003 |  43311 |    4 |
|  10006 |  43311 |    4 |
+--------+--------+------+
6 rows in set (0.00 sec)

24 获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'

select de.dept_no,de.emp_no,s.salary
    FROM dept_emp de inner join employees e on de.emp_no=e.emp_no
    inner join salaries s ON e.emp_no=s.emp_no
    where s.to_date='9999-01-01'
    and de.emp_no NOT IN (select emp_no FROM dept_manager);
+---------+--------+--------+
| dept_no | emp_no | salary |
+---------+--------+--------+
| d001    |  10001 |  98999 |
| d004    |  10003 |  43311 |
| d005    |  10007 |  88070 |
+---------+--------+--------+
3 rows in set (0.00 sec)

注释：需要联合查询4张表 dept_emp，dept_manager, salaries,employees

三个字段:dept_no，和emp_no来自dept_emp，salary来自salaries

两两联合查询关键字emp_no

非manager员工