《学习SQL语句》——第八天

 

# Write your MySQL query statement below

select name, bonus
from Employee left join Bonus 
on Employee.empId = Bonus.empId
where bonus < 1000 or bonus is null;

#从上表体会外连接、全连接、内连接的区别

# Write your MySQL query statement below


select a.name
from Employee a, (select distinct managerId 
                    from Employee 
                    group by managerId
                    having count(managerId) >= 5) as b
where a.id = b.managerId;

#聚合（或聚集、分组、多行）函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。eg:max,count
#where是筛选的对象是，from之后的数据，而where不能作用于聚合函数。
#having是筛选group by后面的数据，having可以对普通字段进行筛选，也可以对聚合函数或者聚合函数的别名进行筛选。

 

 

# Write your MySQL query statement below

select b.name as Department,a.name as Employee,a.salary as Salary
from Employee as a left join Department as b 
on a.departmentId = b.id
where a.id in
(
    select c.id
    from Employee as c left join Employee as d
    on c.departmentId = d.departmentId and c.salary < d.salary
    group by c.id
    having count(distinct d.salary) <= 2
)
and a.DepartmentId in (select id from Department)

#对于这种分组内取前几名的问题，可以先group by然后用having count()来筛选，比如这题，找每个部门的工资前三名，那么先在子查询中用Employee和自己做连接，连接条件是【部门相同但是工资比我高】，那么接下来按照having count(Salary) <= 2来筛选的原理是：如果【跟我一个部门而且工资比我高的人数】不超过2个，那么我一定是部门工资前三，这样内层查询可以查询出所有符合要求的员工ID.