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 后.