1.先創(chuàng)建表
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 別名
此語句表示,根據(jù)cole1分組, 在分組內(nèi)部根據(jù)col2進(jìn)行排序。
而這個(gè)別名表示,每個(gè)組內(nèi)排序的順序編號(hào)(組內(nèi)連接唯一).
4.具體看個(gè)例子
SELECT
empid,
deptid,
salary,
row_number() OVER(PARTITION BY deptid ORDER BY salary DESC) salary_order
FROM employee; 結(jié)果如下
延伸一下, 如果是按某個(gè)字段分組然后從每組取出最大的一條紀(jì)錄,只需加一個(gè)條件,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; 再延伸一下，根據(jù)部門分組，再按部門內(nèi)的個(gè)人薪水排序，逐個(gè)累加。
SELECT
empid,
deptid,
salary,
sum(salary) OVER(PARTITION BY deptid ORDER BY salary DESC) ts
FROM employee 5.partition by 與 group by 的區(qū)別
1).partition by能得到統(tǒng)計(jì)后的明細(xì)數(shù)據(jù)， group by 只能得到匯總數(shù)據(jù)。
2).partition by在from前， group 在 where 后.