牛客网SQL刷题11-20

11、获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_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 `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`));

方法1：使用内连接加上where多条件限制
解题思路：
- 确定连接字段 dept_no；
- 确定筛选条件 dept_manager.to_date = "9999-01-01"以及dept_emp.emp_no不在dept_manager中
select de.emp_no,dm.emp_no from 
dept_emp as de
left join 
dept_manager as dm
on de.dept_no=dm.dept_no
where dm.to_date="9999-01-01"
and de.emp_no not in (select dm.emp_no from dept_manager);

方法2：使用where多条件限制
select de.emp_no,dm.emp_no
from dept_emp as de,dept_manager as dm
where de.dept_no=dm.dept_no
and de.emp_no!=dm.emp_no
and dm.to_date="9999-01-01"
and de.to_date="9999-01-01";

12、获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息，给出dept_no, emp_no以及其对应的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 `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`));

解法：先找出各个部门最高的薪资，再找到各个员工的部门、薪资，联结查询后的两个表，这里筛选时需要两个条件，一个是部门，一个是最高薪资。
SELECT a.dept_no,b.emp_no,a.salary
FROM 
    (SELECT dept_no,MAX(salary) AS salary
    FROM salaries
    INNER JOIN dept_emp
    ON dept_emp.emp_no=salaries.emp_no
    WHERE dept_emp.to_date = '9999-01-01'
    AND salaries.to_date='9999-01-01'
    GROUP BY dept_no) AS a
    ##薪资最高的部门
INNER JOIN ##联合查询
    (SELECT dept_no,dept_emp.emp_no,salary
    FROM dept_emp
    INNER JOIN salaries
    ON dept_emp.emp_no=salaries.emp_no
    WHERE dept_emp.to_date = '9999-01-01'
    AND salaries.to_date='9999-01-01')AS b
    ##查询出员工部门、编号、薪资
ON a.salary=b.salary
AND a.dept_no=b.dept_no
ORDER BY dept_no；

13、从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

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

解答：
select title,count(title) as t 
from titles 
group by title 
having t>=2

此题应注意以下三点：
1、用COUNT()函数和GROUP BY语句可以统计同一title值的记录条数
2、根据题意，输出每个title的个数为t，故用AS语句将COUNT(title)的值转换为t
3、由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>=2的条件

14、从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。

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

解答：
select title,count(distinct emp_no)as t
 from titles group by  title having t>=2;

15、查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列

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`));
解答：
SELECT *FROM employees WHERE emp_no % 2 = 1
AND last_name<>'Mary'
ORDER BY hire_date DESC;

法一：使用%

select *
from employees
where emp_no%2 =1
and last_name!='Mary'
order by hire_date desc
补充：不相等有三种表示方式：<>、!=、IS NOT

法二：使用&
SELECT *
FROM employees
WHERE emp_no & 1
AND last_name<>'Mary'
ORDER BY hire_date DESC;

16、题目：从统计出当前各个title类型对应的员工当前(to_date=‘9999-01-01’)薪水对应的平均工资。结果给出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);

解:法1：
select title,avg(salary) 
from titles as t 
inner join 
salaries as s
on s.emp_no=t.emp_no
and s.to_date='9999-01-01'
and t.to_date="9999-01-01"
group by title;

解法2：
select title,avg(salary) 
from titles as t 
inner join 
salaries as s
on s.emp_no=t.emp_no
and s.to_date='9999-01-01'
and t.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`));
```python
答案：
select emp_no,salary from salaries 
where salary=
(select salary from salaries
where to_date='9999-01-01'
group by salary
order by salary desc limit 1,1);

18、查找所有员工的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`));

方法1：
select last_name,first_name,dept_name
from employees as e left join
(select emp_no,dept_name from dept_emp as de 
left join departments
as d on de.dept_no=d.dept_no)as k
on e.emp_no=k.emp_no;

方法2：
select e.last_name,e.first_name,dept_name from employees as e
left join dept_emp as de on e.emp_no =de.emp_no
left join departments as d on d.dept_no=de.dept_no;

19、查找员工编号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`));

解法1：最高工资-最低工资
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;

解法2：
直接聚合函数，大减小 
select max(salary)-min(salary) growth from salaries where emp_no = 10001;

解法3：
使用左连接的方法，在salaries表的基础上，利用to_date = from_date的约束，给每条数据后面再加上此次涨幅后的工资，然后将两次的工资做差，就知道此次涨薪多少了；也就是说做连接之后表中就记录了涨薪前和涨薪后的工资各为多少，然后做差求和即可。
select sum(s2.salary-s1.salary)
    from salaries s1 , salaries s2
    where s1.emp_no = 10001
    and s1.to_date = s2.from_date

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

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

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

方法：
/*思路：用每位员工当前的薪水-入职的薪水=涨幅growth
每位员工当前的薪水：
SELECT  emp_no,salary
FROM    salaries
WHERE  to_date!='9999-01-01'
每位员工入职时的薪水：
SELECT  e.emp_no,s.salary
FROM    employees AS e
JOIN
         salaries AS s
ON  e.emp_no = s.emp_no
WHERE  e.hire_date = s.from_date*/

正式：
select b.emp_no,(b.salary-a.salary) as growth
from
        (select e.emp_no,s.salary
           from employees e  
         join salaries  s 
         on e.emp_no=s.emp_no
         and e.hire_date=s.from_date)a -- 入职工资表
inner join
        (select e.emp_no,s.salary
           from employees e  
         join   salaries  s 
         on   e.emp_no=s.emp_no
         where s.to_date='9999-01-01')b -- 现在工资表
on a.emp_no=b.emp_no
order by growth