SQL SERVER :删除表中重复的记录:ROW_NUMBER() OVER函数
For example :
ROW_NUMBER() OVER函数的基本用法
语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
简单的说row_number()从1开始,为每一条分组记录返回一个数字,这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序,再为降序以后的没条xlh记录返回一个序号。
示例:
xlh
1700
1500
1085
710
row_number() OVER (PARTITION BY COL1 ORDER BY 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
———– ———– —————————————
1
2
3
4
5
6
7
8
9
需求:根据部门分组,显示每个部门的工资等级
预期结果:
empid
———– ———– ————————————— ——————–
1
2
4
3
7
6
5
9
8
SQL脚本:
SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee
—–
按照table的主键
SELECT * FROM (
select ID=Row_number() over( PARTITION BY ITEM_KEY,STORE_KEY,PERIOD_KEY ORDER BY PERIOD_KEY) ,*
from RSI_FACT ) A
WHERE ID>1
http://blog.sina.com.cn/s/blog_71a536990101c4l4.html