一、条件执行过程
今天学习了where, group by, having, order by的执行过程。他们的执行顺序就是先用where过滤掉不符合条件的数据,再用group by来给剩下的数据进行分组,再用having限定分组后的数据,最后用order by进行排序。所以他们的顺序就是:where-->group by-->having-->order by。
注意:having后跟的条件可以有聚合函数(max, min之类的函数), 但是where后的条件不可是聚合函数。
二、sql练习
这个练习是从LeetCode上看到的题目。
现有员工表Employee和部门表Department,要取出每个部门最高工资的员工姓名,所在部门和工资。
下面是初始化语句。create table Employee
(
id int not null,
name VARCHAR2(64) not null,
salary VARCHAR2(256) not null,
DepartmentId int not null
)
create table Department
(
id int not null,
name VARCHAR2(64) not null
)
insert into Employee (id, name, salary, DepartmentId) values (1, 'Joe', '70000', 1);
insert into Employee (id, name, salary, DepartmentId) values (2, 'Henry', '80000', 2);
insert into Employee (id, name, salary, DepartmentId) values (3, 'Sam', '60000', 2);
insert into Employee (id, name, salary, DepartmentId) values (4, 'Max', '90000', 1);
insert into Department (id, name) values (1, 'IT');
insert into Department (id, name) values (2, 'Sales');
首先我用下面语句获得想要的结果:select d.name as "Department", e.name as "Employee", e.salary as "Salary"
from Employee e, Department d
where e.DepartmentId = d.id
and not exists(
select salary
from Employee em
where em.salary > e.salary
and em.DepartmentId = e.DepartmentId
)
查出员工表与部门表中相对应的员工部门,员工名字和工资,最后用“不存在比他薪水还大的工资”这一条件来限定我所查出来的结果是工资最高的员工数据。
第二种解法:select d.name as "Department", e.name as "Employee", e.salary as "Salary"
from Employee e
left join Department d
on
d.id = e.DepartmentId
where(e.DepartmentId, e.salary) in
(select ie.DepartmentId, max(ie.salary)
from Employee ie, Department id
group by ie.DepartmentId)
用左连接查出员工及其对应部门的信息,在用子查询限定结果必须是在其部门中新子最高的。
再看一题:找出每个部门工资前三高的员工,输出其部门名,名字,薪水。基于上述表,添加下列两条记录insert into Employee (id, name, salary, DepartmentId) values (5, 'Janet', '69000', 1);
insert into Employee (id, name, salary, DepartmentId) values (6, 'Randy', '85000', 1);
做法:select Department, Employee, Salary
from (select d.name as Department,
e.Name as Employee,
e.Salary,
dense_rank() over(partition by d.id order by e.salary desc) as rn
from Department d
left join Employee e
on d.Id = e.DepartmentId) l
where l.rn <= 3
此处用的是oracle中的开窗函数,也是因为用开窗函数比较简单,所以用这个。