ITPub博客

首页 > Linux操作系统 > Linux操作系统 > over 开窗函数的的理解

over 开窗函数的的理解

原创 Linux操作系统 作者:guan_jh 时间:2009-08-05 17:09:52 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-18

  • 博文量
    15
  • 访问量
    29909