1.先创建表
create table employee (empid int ,deptid int ,salary decimal(10,2));2.插入记录
insert into employee values(1,10,5500.00);
insert into employee values(2,10,4500.00);
insert into employee values(3,20,1900.00);
insert into employee values(4,20,4800.00);
insert into employee values(5,40,6500.00);
insert into employee values(6,40,14500.00);
insert into employee values(7,40,44500.00);
insert into employee values(8,50,6500.00);
insert into employee values(9,50,750 0.00); 3.语句讲解
row_number() over([partition by col1] order by col2)) as 别名
此语句表示,根据cole1分组, 在分组内部根据col2进行排序。
而这个别名表示,每个组内排序的顺序编号(组内连接唯一).
4.具体看个例子
SELECT
empid,
deptid,
salary,
row_number() OVER(PARTITION BY deptid ORDER BY salary DESC) salary_order
FROM employee; 结果如下
延伸一下, 如果是按某个字段分组然后从每组取出最大的一条纪录,只需加一个条件,salary_order=1
sql代码如下:
SELECT
t1.empid,
t1.deptid,
t1.salary
FROM (
SELECT
empid,
deptid,
salary,
row_number() OVER(PARTITION BY deptid ORDER BY salary DESC) salary_order
FROM employee
) t1
WHERE t1.salary_order=1; 再延伸一下，根据部门分组，再按部门内的个人薪水排序，逐个累加。
SELECT
empid,
deptid,
salary,
sum(salary) OVER(PARTITION BY deptid ORDER BY salary DESC) ts
FROM employee 5.partition by 与 group by 的区别
1).partition by能得到统计后的明细数据， group by 只能得到汇总数据。
2).partition by在from前， group 在 where 后.