ROW_NUMBER() OVER 用处
来源:互联网 发布:网络唤醒主板设置 编辑:程序博客网 时间:2024/05/18 02:45
ROW_NUMBER() OVER
1,分组排序
2,删除重复行
CREATE TABLE album
(
Id int identity (1,1),
song_name varchar(1000),
singer varchar(100),
Insert_DT datetime
)
INSERT album VALUES ('song1','singer1', GETDATE()-1)
INSERT album VALUES ('song2','singer2', GETDATE()-2)
INSERT album VALUES ('song2','singer2', GETDATE()-3)
INSERT album VALUES ('song4','singer4', GETDATE()-4)
INSERT album VALUES ('song4','singer4', GETDATE()-5)
INSERT album VALUES ('song4','singer4', GETDATE()-6)
INSERT album VALUES ('song3','singer3', GETDATE()-7)
select * from album
select Rnum=row_number() over (partition by song_name, singer order by Insert_dt),*
from album
with [cte duplicate] as
(
select Rnum=row_number() over (partition by song_name, singer order by Insert_dt),*
from album
)
delete from [cte duplicate] where Rnum > 1
select * from album
--drop table album
- ROW_NUMBER() OVER 用处
- ROW_NUMBER OVER()
- ROW_NUMBER() OVER()
- ROW_NUMBER() OVER
- row_number() OVER
- row_number() over()
- ROW_NUMBER() over
- row_number() OVER(
- ROW_NUMBER() OVER()
- ROW_NUMBER() OVER ,ROWNUM , OVER()
- rank() over和row_number() over
- row_number() over()用法
- row_number over 函数
- ROWNUM 与ROW_NUMBER()OVER()
- DB2分页ROW_NUMBER() over()
- ROW_NUMBER() OVER 函数使用方法
- row_number() over(PARTITION BY
- oracle row_number over
- LINQ to SQL之增删改查
- 数据结构课程设计---------最少换车次数问题
- 厚积薄发———jsp中读取blob字段&oracle
- Java开源建站工具
- 模拟地和数字地的处理
- ROW_NUMBER() OVER 用处
- java编码规范
- 厚积薄发----Oracle SQL学习笔记
- 你所不知道的8020法则
- 集成过程输出验证的目标——DO-178B/ED-12B学习笔记之十六
- 修改稿
- 编写高效的Android代码
- 三、类以及其继承
- Hibernate one2one 主键单向关联