# ROW_NUMBER() OVER函数的基本用法，也可用于去除重复行

xlh           row_num
1700              1
1500              2
1085              3
710                4

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

create table employee (empid int ,deptid int ,salary decimal(10,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,7500.00)

empid       deptid      salary
----------- ----------- ---------------------------------------
1           10          5500.00
2           10          4500.00
3           20          1900.00
4           20          4800.00
5           40          6500.00
6           40          14500.00
7           40          44500.00
8           50          6500.00
9           50          7500.00

SQL脚本：

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

empid       deptid      salary                                  rank
----------- ----------- --------------------------------------- --------------------
1           10          5500.00                                 1
2           10          4500.00                                 2
4           20          4800.00                                 1
3           20          1900.00                                 2
7           40          44500.00                                1
6           40          14500.00                                2
5           40          6500.00                                 3
9           50          7500.00                                 1
8           50          6500.00                                 2

select empid,deptid,rank from
(SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee) a
where rank<=1

empid       deptid                                rank
----------- ----------- ------------------------------
1           10                                          1
4           20                                          1
7           40                                          1
9           50                                          1

posted @ 2017-05-03 19:48  Twang  阅读(1054)  评论(0编辑  收藏