over 开窗函数的的理解

over的功能：

一、

with Ta as
(
select 1 as Fa, 'a' as Fb
union all
select 2 as Fa, 'b' as Fb
union all
select 3 as Fa, 'c' as Fb
union all
select 4 as Fa, 'd' as Fb
union all
select 5 as Fa, 'e' as Fb

)
select FB ,1.*Fa/(select sum(Fa) from Ta)
from Ta

with Ta as
(
select 1 as Fa, 'a' as Fb
union all
select 2 as Fa, 'b' as Fb
union all
select 3 as Fa, 'c' as Fb
union all
select 4 as Fa, 'd' as Fb
union all
select 5 as Fa, 'e' as Fb
)
select FB ,1.*Fa/sum(Fa) over()
from Ta

二、配合row_number()函数使用

SELECT CONTRACT_BH,CONTRACT_ID ,ROW_NUMBER()OVER(ORDER BY JKR_CUST_ID) AS ROWNUMBER
FROM TCOMMCREDIT
WHERE ROWNUMBER <=  10

 

三、推广 row_number() 到rank

USE a;
GO
SELECT A.CONTRACT_BH, A.CONTRACT_ID, B.CUST_NAME, RANK() OVER (PARTITION BY A.JKR_CUST_ID order by A.CONTRACT_ID) as RANK
FROM TCOMMCREDIT A ,TENTCUSTINFO B
WHERE A.JKR_CUST_ID = B.CUST_ID
GO

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20854791/viewspace-611425/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20854791/viewspace-611425/