牛客网SQL实战练习——21~25
21.查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
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`));
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`));
分析： 对于每个员工，自入职以来的薪水涨幅是 ：当前的薪水-入职时的薪水。先取得当前时间的工资：把employees 表 e与 salaries 表通过 e.to_date="9999-01-01" 用 inner join 进行连接。接下来，取得入职时的工资，把employees 表 e与 salaries 表 s 通过 e.hire_date=s. from_date 用 inner join 进行连接。
答案：
select e.emp_no, (a.salary-b.salary) as growth
from employees as e
inner join salaries as a
on e.emp_no=a.emp_no and a.to_date='9999-01-01'
inner join salaries as b
on e.emp_no=b.emp_no and b.from_date=e.hire_date
order by growth asc
22.统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及次数sum
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 `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`));
分析：本题中涉及三个表，首先用dept_no把dept_emp和departments两个表链接起来，用emp_no把dept_emp和salary两个表链接起来。其中用count(s.salary)记录工资次数，按各个部门区分即group by d.dept_no。
答案：
select d.dept_no,d.dept_name,count(s.salary) as sum
from departments as d,dept_emp as de,salaries as s
where d.dept_no=de.dept_no and de.emp_no=s.emp_no
group by d.dept_no
23.对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_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`));
分析：两张相同的salaries表（分别为s1与s2）进行对比分析，先将两表限定条件设为to_date = '9999-01-01'，挑选出当前所有员工的薪水情况。使用count,大于等于该条salary的数据条数，又因为数据有重复，所以distinct，此处必须使用表的重复使用功能。按s1.emp_no 分组，group by s1.emp_no ，最后先以 s1.salary 逆序排列，再以 s1.emp_no 顺序排列输出结果。
答案：
select s1.emp_no,s1.salary,count(distinct s2.salary) as rank
from salaries as s1,salaries as s2
where s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
and s1.salary<=s2.salary
group by s1.emp_no
order by s1.salary desc,s1.emp_no asc
24.获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'
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 `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`));
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`));
分析：先用INNER JOIN连接employees和salaries，找出当前所有员工的工资情况,再用INNER JOIN连接dept_emp表，找到所有员工所在的部门,最后用限制条件de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')选出当前所有非manager员工，再依次输出dept_no、emp_no、salary.
答案：
select de.dept_no,s.emp_no,s.salary
from dept_emp as de inner join salaries as s
on de.emp_no=s.emp_no
where s.to_date='9999-01-01'
and de.emp_no not in(
select emp_no
from dept_manager
where to_date='9999-01-01'
)
25.获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_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 `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 `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`));
分析：复用salaries表，获取员工（不是manager）的当前薪水（表一），获取manager的当前薪水（表二），.连接表一和表二，筛选出员工当前工资>manager当前工资。
答案：
select de.emp_no,dm.emp_no,s1.salary,s2.salary
from dept_emp as de ,dept_manager as dm,salaries as s1,salaries as s2
where de.dept_no=dm.dept_no
and de.emp_no = s1.emp_no
and dm.emp_no = s2.emp_no
and s1.to_date='9999-01-01'
and s2.to_date='9999-01-01'
and s1.salary > s2.salary
欢迎关注微信公众号：蛋炒番茄
同步更新！！！
