ROW_NUMBER SQL Server 2005的LIMIT功能实现(ROW_NUMBER()排序函

复制代码 代码如下:

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY id ASC) AS rownum, * FROM MyTable) AS items WHERE items.rownum BETWEEN 20 AND 30;

复制代码 代码如下:

select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer

复制代码 代码如下:

select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order

复制代码 代码如下:

select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order

复制代码 代码如下:

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

复制代码 代码如下:

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
)

复制代码 代码如下:

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

复制代码 代码如下:

select
ROW_NUMBER() over(partition by customerID order by insDT) as rows,
customerID,totalPrice, DID
from OP_Order where insDT>'2011-07-22'