第11题获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。


select de.emp_no, 
       dm.emp_no as manager_no
from dept_emp de
inner join dept_manager dm
on de.dept_no=dm.dept_no
where de.to_date='9999-01-01'
and dm.to_date='9999-01-01'
and de.emp_no <> dm.emp_no
;

解析：这里用左连接和内连接都可以

第12题获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

select de.dept_no, 
       de.emp_no, 
       max(s.salary)
from dept_emp de
left join salaries s
on de.emp_no=s.emp_no
where de.to_date='9999-01-01' and s.to_date='9999-01-01'
group by de.dept_no
;

解析：group by 对部门进行分组，max()函数取每组里面的最大值

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

select title, count(1) as t
from titles
group by title
having t >= 2
;

解析：having t >=2 ，对count后的数据进行过滤

第14题从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略。

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

解析：对emp_no 去重，直接distinct就可以了

第15题查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

select *
from employees 
where emp_no%2=1
and last_name <> 'Mary'
order by hire_date desc
;

解析：这道题很简单，奇数就是模2余1，注意看下数据格式，如果不是int类型，需要先用cast转数据类型

第16题统计出当前各个title类型对应的员工当前（to_date='9999-01-01'）薪水对应的平均工资。结果给出title以及平均工资avg。

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

解析：这道题主要是考察对avg()函数的使用，求平均数。

第17题获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no, salary
from salaries
order by salary desc
limit 1,1
;

解析：薪水第二多的员工，用 limit1,1 表示

第18题查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

SELECT e.emp_no
     , s.salary
     , e.last_name
     , e.first_name
FROM employees e
LEFT JOIN salaries s
ON e.emp_no=s.emp_no
WHERE s.salary = (
    SELECT MAX(salary) 
    FROM salaries
    WHERE to_date='9999-01-01'
    AND salary < (SELECT MAX(salary) FROM salaries)
)
;

解析：找到薪水排名第一的员工，然后排名仅次于第一的员工就是排名第二的员工，两个子查询搞定。

第19题查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

SELECT a.last_name
     , a.first_name
     , dp.dept_name
FROM (
    SELECT e.last_name
         , e.first_name
         , de.dept_no
    FROM employees e
    LEFT JOIN dept_emp de
    ON e.emp_no=de.emp_no
) a
LEFT JOIN departments dp
ON dp.dept_no=a.dept_no
;

解析：这道题也很简单，搞清楚表结构后，两次 left join 即可求出。

第20题查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

SELECT MAX(salary) - MIN(salary) AS growth
FROM salaries
WHERE emp_no='10001'
;

其他解法二：

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;
FROM salaries;

解析：这道题有两种理解：一是涨幅值为最大薪资值减最小薪资值，二是现在的工资减入职时的工资，考虑到可能存在降薪，题解二的理解是更符合实际的，但是sql不通过。。这里提供这种思考方式，万一面试问到了呢。

posted @ 2020-03-14 23:57 水木青楓阅读(120) 评论(0) 编辑收藏举报

刷新页面返回顶部

行勝於言

行勝於言desc

牛客网数据库SQL实战解析(11-20题)

公告