over 开窗函数的的理解-优快云博客

本文详细介绍了SQL中的窗口函数over的应用场景和技术细节，包括如何利用over进行比例计算、结合row_number()函数实现行号分配，以及扩展到rank()函数进行分组排名等高级用法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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/