Oracle中where,group by, having, o

一、条件执行过程

今天学习了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中的开窗函数，也是因为用开窗函数比较简单，所以用这个。