牛客網(wǎng)SQL實戰(zhàn)練習——16~20

聲明：練習?？途W(wǎng)SQL實戰(zhàn)題目，整理筆記。
16.統(tǒng)計出當前各個title類型對應的員工當前（to_date='9999-01-01'）薪水對應的平均工資。結(jié)果給出title以及平均工資avg。

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`));
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

分析：本題中的兩個表需要用inner by連接起來，平均薪水使用avg(s.salary) ，各個title類型group by title
答案：

select t.title,avg(s.salary) 
from titles as t inner join salaries as s
on t.emp_no=s.emp_no
where t.to_date='9999-01-01' and s.to_date='9999-01-01'
group by title

17.獲取當前（to_date='9999-01-01'）薪水第二多的員工的emp_no以及其對應的薪水salary

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`));

分析：獲取薪水第二多的員工信息，需要將salary進行排序，然后使用limit提出第二個薪水多的員工信息即可，即order by salary desc limit 1,1
答案：

select emp_no,salary
from salaries
where to_date='9999-01-01'
order by salary desc limit 1,1

18.查找當前薪水(to_date='9999-01-01')排名第二多的員工編號emp_no、薪水salary、last_name以及first_name，不準使用order by

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`));

分析：本題中明確指出不能使用order by，所以不能將salary進行排序，換種思路，查找排名第二多的員工信息，可以排除salary最高的員工，剩下的salary最高的便是第二高的，故使用max與not in和嵌套子查詢完成。
答案：

select e.emp_no,max(s.salary) as salary,e.last_name,e.first_name
from employees as e inner join salaries as s
on e.emp_no=s.emp_no
where s.to_date='9999-01-01' 
and s.salary not in(
    select max(salary) 
    from salaries
    where to_date='9999-01-01'
)

19.查找所有員工的last_name和first_name以及對應的dept_name，也包括暫時沒有分配部門的員工

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 `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`));

分析：第一步，將員工表employees與員工-部門關(guān)系表dept_emp進行外連接left join，連接條件是員工編號對應，這樣得到的連表包含所有的員工信息+對應的部門編號信息（如果員工未分配部門，則按照外連接規(guī)則，默認填寫null）；
第二步，將第一步中得到的連表，與部門信息表departments進行外連接，連接條件是部門編號對應，這樣得到的新的連表即包含題目要求的所有記錄。
答案：

select e.last_name,e.first_name,d.dept_name
from employees e 
left join dept_emp de on e.emp_no = de.emp_no
left join departments d on de.dept_no = d.dept_no

20.查找員工編號emp_no為10001其自入職以來的薪水salary漲幅值growth

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`));

分析：首先分別找到emp_no=10001的員工的第一次工資記錄與最后一次工資記錄，再將最后一次工資記錄減去第一次工資記錄得到入職以來salary的漲幅，最后用別名growth代替。
答案：

select 
(
(select salary from salaries 
 where emp_no = 10001
 order by to_date desc
 limit 1
 )
 -
(select salary from salaries
 where emp_no = 10001
 order by to_date asc
 limit 1
 )
)
 as growth

歡迎關(guān)注微信公眾號：蛋炒番茄
同步更新！??！