row_number()over(partition by)函数实际应用 原创

row_number()over(partition by)函数实际应用

A产品表 BIFS_FINANCIAL_PRODUCT
B月末净值表 BIFS_NETVALUE_THEDAY
A表 和 B表是一对多的关系，通过FPID关联。
需求：需要查询出产品表对应的最新的月末净值的数据，推送给第三方系统。
实现：可以用 row_number()over(partition by xx) order by xx)

复制收展SQLselect T.ID               as "id",
       T.FPID             as "fpid",
       BFP.FINANCE_NO     as "productNo",
       BFP.FINANCE_NAME   as "productName",
       BFP.BUSS_TYPE      as "bussType",
       T.NET_VALUE        as "netValue",
       T.MAINTENANCE_DATE as "theTime",
       T.EARNINGS         as "earnings",
       T.CREATE_TIME      as "createTime",
       T.LAST_UPDATE_TIME as "updateTime"
from BIFS_FINANCIAL_PRODUCT BFP
         left join (
    select A.*, row_number() over (partition by A.FPID order by A.MAINTENANCE_DATE desc ) IDX
    from BIFS_NETVALUE_THEDAY A
    where 1 = 1
      and trunc(A.MAINTENANCE_DATE) < trunc(sysdate)
) T ON T.FPID = BFP.FP_ID and T.IDX = 1
where 1 = 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18