牛客网数据库sql实战剖析(二）

1.查找employees表emp_no与last_name的员工信息

思路：where条件判断即可

select emp_no,birth_date,first_name,last_name,gender,hire_date
from employees
where emp_no%2!=0 and last_name!="Mary"
order by hire_date desc

补充：不相等有三种表示方式：<>、!=、IS NOT

2.统计出当前各个title类型对应的员工当前薪水对应的平均工资

思路：先分组后排序

select t.title,avg(s.salary) 
from titles t
inner join salaries s
on t.emp_no=s.emp_no
group by t.title
order by avg(s.salary) 

3.获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

思路：因为有重复的最高salary，所以先找到最高薪水（这里可能薪水最高的有多个，通过分组的方式来去重，也可以直接distinct），然后再进一步select。这里的limit 1 offset 1也可以写成limit 1,1，表示跳过一条读一条数据。

select emp_no,salary
from salaries 
where salary 
= (select salary
from salaries
#这里可能薪水最高的有多个，所以先进行分组
group by salary
order by salary desc
limit 1 offset 1)
或者

4. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary（不能使用order by #)

思路：
方法1.可以找到最高工资的人，然后查出除了原表最高工资以外的最高工资，最后将第二高工资作为查询条件。(找到第二高工资之后只是作为筛选条件，还需要再用salary做一次join)
方法2.通过自连接，on条件中当s1<=s2链接并以s1.salary分组时一个s1会对应多个s2，对s2去重之后的数量就是对应的名次。（#）

第一种：
select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s join employees e
on s.emp_no = e.emp_no
where s.salary =              -- 第三步: 将第二高工资作为查询条件
    (
    select max(salary)        -- 第二步: 查出除了原表最高工资以外的最高工资(第二高工资)
    from salaries
    where salary <   
        (
        select max(salary)    -- 第一步: 查出原表最高工资
        from salaries
        where to_date = '9999-01-01'  
        )
    and to_date = '9999-01-01'
    )
and s.to_date = '9999-01-01'
第二种 通用型可以求任意第几高，并且可以求多个形同工资
select e.emp_no,s.salary,e.last_name,e.first_name
from
employees e
join 
salaries s on e.emp_no=s.emp_no 
and  s.to_date='9999-01-01'
and s.salary = 
(
     select s1.salary
     from 
     salaries s1
     join
     salaries s2 on s1.salary<=s2.salary 
     and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
     group by s1.salary
     having count(distinct s2.salary)=2
 )

5.查找所有员工的last_name和first_name以及对应的dept_name

思路：可以先后使用两次left join，也可以使用left join嵌套来实现。

方法1：先后left join
select e.last_name,e.first_name,m.dept_name 
from employees e
left join dept_emp d
on e.emp_no=d.emp_no
left join departments m
on d.dept_no=m.dept_no
方法2：left join嵌套
SELECT last_name, first_name, dept_name
FROM (SELECT *
FROM employees
LEFT JOIN dept_emp
ON employees.emp_no=dept_emp.emp_no
) AS a
LEFT JOIN departments
ON a.dept_no=departments.dept_no;

6.查找在职员工自入职以来的薪水涨幅情况

思路：拼出入职的工资表和现在的工资表，然后两者工资相减。where s.to_date='9999-01-01’也可以写成on的连接条件。

select b.emp_no,(b.salary-a.salary) as growth
from
(select e.emp_no,s.salary
from employees e left 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 left 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

7.统计各个部门的工资记录数

思路：两次join可以连着写，另外只能根据dt.dept_no分组，不能用de.dept_no分组，有点奇怪~

select dt.dept_no,dt.dept_name,count(dt.dept_no) sum
from departments dt
left join dept_emp de on dt.dept_no=de.dept_no
left join salaries s on de.emp_no=s.emp_no
group by dt.dept_no,dt.dept_name
order by dt.dept_no ASC

8.SQL217 对所有员工的薪水按照salary降序进行1-N的排名

思路：难点在于排名的具体值怎么体现。
方法1.通过使用比较的方式找出大于某个人的工资数就是排名了，注意去重（同名并列）。

select
    s1.emp_no,
    s1.salary,
    (select
    count(distinct s2.salary)
    from salaries s2
    where s2.salary >= s1.salary
    ) as `t_rank`
from salaries s1
order by `t_rank`,s1.emp_no

方法2.这道题目考察的是SQL窗口函数（OLAP函数）中用于排序的专用窗口函数用法
但是由于关系数据库提供支持OLAP用途功能时间不长
还有一部分DBMS不支持这个新功能（比如MYSQL）

select emp_no, salary,
       dense_rank() over (order by salary desc) as rank
from salaries
where to_date='9999-01-01'
order by rank asc,emp_no asc;

下面介绍三种用于进行排序的专用窗口函数：

1、RANK()
在计算排序时，若存在相同位次，会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，4······

2、DENSE_RANK()
这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，2······

3、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如，有3条排在第1位时，排序为：1，2，3，4······

窗口函数用法：
<窗口函数> OVER ( [PARTITION BY <列清单> ]
ORDER BY <排序用列清单> ）
*其中[ ]中的内容可以忽略

9.获取所有非manager员工当前的薪水情况

思路：
方法1：使用not in

select 
a.dept_no,a.emp_no,s.salary
from (select 
*
from dept_emp e
where e.emp_no not in (select emp_no from dept_manager)) a
left join salaries s
on a.emp_no=s.emp_no

方法2：左连接，然后判断emp_no is null的即为非manager

10.获取员工其当前的薪水比其manager当前薪水还高的相关信息

思路：
分别获取员工和管理者的工资表，然后使用部门号连接，并且增加员工工资>经理工资的筛选条件。

**联结表emp_sal和表mag_sal，连接条件部门编号相等，要求：员工工资>经理工资**
select emp_sal.emp_no,mag_sal.manager_no,
emp_sal.emp_salary,mag_sal.manager_salary
from (
    select de.emp_no,de.dept_no,s1.salary as emp_salary
    from dept_emp de,salaries s1
    where de.emp_no=s1.emp_no
    and s1.to_date='9999-01-01'
    and de.to_date='9999-01-01'
)as emp_sal
inner join(
    select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
    from dept_manager dm,salaries s2
    where dm.emp_no=s2.emp_no
    and s2.to_date='9999-01-01'
    and dm.to_date='9999-01-01'
)as mag_sal
on emp_sal.dept_no=mag_sal.dept_no
where mag_sal.manager_salary<emp_sal.emp_salary;

11.汇总各个部门当前员工的title类型的分配数目

思路：总之就是一顿连接，而且要注意是对谁分组。
经验：遇到比较多的表，把连接关系列出来，把结果设计到的字段也确定好列在纸上，可以更好的理清逻辑。

select dm.dept_no,dm.dept_name,title,count(t.title) count
from departments dm
left join dept_emp de
on dm.dept_no=de.dept_no
left join titles t
on de.emp_no=t.emp_no 
group by dm.dept_no,t.title  //注意这里是针对dm的dept_no进行分组，对de就不行了。。。
order by dept_no,title;

12.使用join查询方式找出没有分类的电影id以及名称

思路：左连接，找出category_id为null的即为未分类。同样纸上列出关系表以及结果表，更快解决问题。
如：film–>film_id,title category–>category_id,name f_c—>f_id,c_id 结果要求：f_id title

select f.film_id,f.title
from film f
left join film_category fc
on f.film_id=fc.film_id
left join category c
on c.category_id=fc.category_id
where c.category_id is null;

13.使用子查询的方式找出属于Action分类的所有电影对应的title,description

思路：内连接inner join，可以简写成join（返回两个表中都满足条件的行，即交集。注意区分全连接），子查询

select f.title,f.description
from film f
join film_category fc
on f.film_id=fc.film_id
join ( 
    select category_id 
    from category
    where name="Action"
 ) filterCate
on fc.category_id=filterCate.category_id

#或者使用in的方式
# select title,description
# from film f
# where f.film_id in (select fc.film_id
#                     from category c join film_category fc on c.category_id=fc.category_id
#                     where name='Action')