SQL的ROW_NUMBER函数
标签：
SQL的ROW_NUMBER函数
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
)
注意：在使用over等开窗函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行
select
ROW_NUMBER() over(partition by customerID order by insDT) as rows,
customerID,totalPrice, DID
from OP_Order where insDT>‘2011-07-22‘