SQL Server ROW_NUMBER SQL Server 2005的LIMIT功能实现(ROW_NUMBER()排序函数)
2022-11-12 09:49:41
内容摘要
这篇文章主要为大家详细介绍了SQL Server ROW_NUMBER SQL Server 2005的LIMIT功能实现(ROW_NUMBER()排序函数),具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看
文章正文
这篇文章主要为大家详细介绍了SQL Server ROW_NUMBER SQL Server 2005的LIMIT功能实现(ROW_NUMBER()排序函数),具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
语法:ROW_NUMBER() OVER([ <partition_by_clause>] <order_by_clause>)备注:ORDER BY子句可确定在特定分区中为行分配唯一ROW_NUMBER的顺序。参数:<partition_by_clause> 将FROM子句生成的结果集划入应用了ROW_NUMBER函数的分区。<order_by_clause> 确定将ROW_NUMBER值分配给分区中的行的顺序。有关详细信息,请参阅ORDER BY子句(Transact-SQL)。返回类型:bigint类似于MySQL的LIMIT功能语法实例:代码如下:
1 2 3 | <code> SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY id ASC) AS rownum, * FROM MyTable) AS items WHERE items.rownum BETWEEN 20 AND 30; </code> |
代码如下:
1 2 3 | <code> select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer </code> |
代码如下:
1 2 3 | <code> select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order </code> |
代码如下:
1 | <code>select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order</code> |
代码如下:
1 2 3 4 5 6 7 | <code> with tabs as ( select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order ) select MAX(rows) as '下单次数' ,customerID from tabs group by customerID </code> |
代码如下:
1 2 3 4 5 6 7 8 9 10 11 | <code> with tabs as ( select ROW_NUMBER() over(partition by customerID order by insDT) as rows,customerID,totalPrice, DID from OP_Order ) select * from tabs where totalPrice in ( select MIN(totalPrice)from tabs group by customerID ) </code> |
代码如下:
1 2 3 4 5 6 7 8 | <code> with tabs as ( select ROW_NUMBER() over(partition by customerID order by insDT) as rows,* from OP_Order ) select * from tabs where rows = 1 select * from OP_Order </code> |
代码如下:
1 2 3 4 5 6 | <code> select ROW_NUMBER() over(partition by customerID order by insDT) as rows, customerID,totalPrice, DID from OP_Order where insDT> '2011-07-22' </code> |
注:关于SQL Server ROW_NUMBER SQL Server 2005的LIMIT功能实现(ROW_NUMBER()排序函数)的内容就先介绍到这里,更多相关文章的可以留意
代码注释