1.先创建表
2.插入记录
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 );
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;
结果如下
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 ;
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
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 后.