Row_Number()over(order by....) as
来源:互联网 发布:truelicense 源码下载 编辑:程序博客网 时间:2024/05/29 18:09
现在有一张如下图所示的表,想对重复的orderid 进行去重,看同事的代码时看到了rownumber() over(partition by col1 order by col2)去重的方法很好,分享给大家。
1.查询所有,如果orderid 相同,则按照price 排序
select orderid,foodName,price ,ROW_NUMBER() OVER(PARTITION BY orderid order by price desc) from Table_3;
得到的结果如下:数据表先按照orderid 分组,在分组内按照price降序排序。
row_number()函数在分组内部排序后的顺序编号(组内连续唯一的)。
2.如果想得到不重复的orderid,让每一个orderid 只得到一条数据,则可使用下面的语句:
SELECT s.* FROM (SELECT orderid,foodName,price ,ROW_NUMBER() OVER( PARTITION BY orderid ORDER BY price DESC)as rowid FROM Table_3 )s WHERE s.rowid= 1;
由查询结果可知,相同orderid 的只保留了rowid=1 的记录,其他的都被过滤掉了,从而达到了对orderid 去重处理。
3.跳跃排序
Rank() over(partition by col1 order by col2)
select *, rank() over(partition by orderid order by price desc) from Table_3;
由查询结果可知,相同的并列,下一个则跳跃到并列所替的序列后:如有两个并列1,那么下一个则直接排为3,跳过2;
4.连续排序
DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2)
select *, dense_rank() over(partition by orderid order by price desc) from Table_3;
由查询结果可知,当两个并列为1时,下一个仍连续有序为2,不跳跃到3
5.sum() over (partition by col1 order by col2)
参考:http://blog.csdn.net/zengmingen/article/details/50786229。
阅读全文
0 0
- Row_Number()over(order by....) as
- SqlServer 分页 ROW_NUMBER() OVER(Order by * DESC ) AS RowNumber
- select row_number() over (order by ItemID asc) as RowIndex
- ROW_NUMBER() over(order by Id)
- ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)
- row_number() over (partition by....order by...)用法
- row_number() over (partition by....order by...)用法
- row_number() over (partition by....order by...)用法
- row_number() over(partition by 列名1 order by 列名2 desc1) as rank
- Actual Practise : Row_Number() over(partition by colnum1 order by colnum2 ) as No - 7
- SQL 分页支持查询 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
- SQL 分页支持查询 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
- SQL 分页支持查询 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
- SQL 分页支持查询 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo_
- SQL 分页支持查询 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
- select row_number() over (order by [COLLECTORID]) as no,'26','01',GETDATE(),[COLLECTORID],'0'
- Server 2005 ROW_NUMBER() over(order by *) 使用
- row_number() over(order by) 与count(1)
- 博主声明
- python类和对象
- 【清华集训2017模拟11.29】K小数查询
- django-admin.py startproject testdj 失败 没有工程文件夹 弹出admin.py
- eclipse安装ADT失败解决办法?自己下载ADT压缩包安装
- Row_Number()over(order by....) as
- Twitter分布式唯一ID算法
- 梯度下降优化算法综述
- linux配置固定的ip地址
- 在Eclipse中使用Maven构建Spring项目
- 深入FFM原理与实践(美团点评技术)
- 给定一个数组A[0,1,...,n-1],请构建一个数组B[0,1,...,n-1],其中B中的元素B[i]=A[0]*A[1]*...*A[i-1]*A[i+1]*...*A[n-1]。不能使用除法
- python算法实战5
- hdoj 2036 改革春风吹满地