MySql — partition by和group by对比

SQL语句优先级

• FROM > WHERE > GROUP BY > HAVING > ORDER BY > PARTITION BY

  partition by:在保留全部数据的基础上，只对其中某些字段做分组排序

  group by:只保留参与分组的字段和聚合函数的结果

  

执行group by之后

SELECT name , count(*) 
FROM t_student 
GROUP BY name

创建员工表，包含姓名、薪资、部门

CREATE TABLE Employee
(
  ID number(10) not null primary key,
  EmpName varchar(20),
  EmpSalary varchar(10),
  EmpDepartment varchar(20)
);

insert all into Employee values(1,'张三','5000','开发部')
into Employee values(2,'李四','2000','销售部')
into Employee values(3,'王麻子','2500','销售部')
into Employee values(4,'张三表叔','8000','开发部')
into Employee values(5,'李四表叔','5000','开发部')
into Employee values(6,'王麻子表叔','5000','销售部')
select 1 from dual

1. 利用GROUP BY统计每个部门的薪资总和：

SELECT EmpDepartment , sum(EmpSalary) sum_salary
FROM Employee
GROUP BY EmpDepartment

2. 利用Partition BY

SELECT EmpSalary,EmpDepartment,SUM(EmpSalary) 
OVER(PARTITION BY EmpDepartment) sum_sala 
FROM Employee

• PARTITION BY结合ORDER BY的高级用法

  SELECT EmpSalary,EmpDepartment,SUM(EmpSalary)
  OVER(PARTITION BY EmpDepartment ORDER BY EmpSalary) sum_sala 
  FROM Employee