当我们遇到这种情况需要处理数据时可以使用这个函数:
查询结果中根据相同字段的数据再根据特定字段取取值较大或较小的一条返回
`SELECT * FROM
(SELECT a.,Row_Number() Over(partition by a.pfvtId ORDER BY a.mcalEvent desc) as rn FROM*
(SELECT
(select TO_DATE(TO_CHAR(sysdate,‘yyyy-mm-dd’),‘yyyy-mm-dd’) from dual) - TO_DATE(TO_CHAR((SELECT MAX(pfs.PFFS_SYSTEM_TIME) FROM PF_FEE_STATUS pfs WHERE pfs.PFFS_PFCO_ID =PF_COST.PFCO_ID AND pfs.PFFS_STATUS = ‘FEE_INVOICED’),‘yyyy-mm-dd’),‘yyyy-mm-dd’) mcalEvent
FROM PF_VOTE
LEFT JOIN PF_COST ON PF_VOTE.PFVT_ID = PF_COST.PFCO_PFVT_ID
LEFT JOIN EB_PROJECT ebpj ON PF_COST.PFCO_EBPJ_ID = ebpj.EBPJ_ID
LEFT JOIN PO_BATCH pba ON PF_COST.PFCO_POBA_ID = pba.POBA_ID)
)a
)t
WHERE
t.rn = 1;
结果:
语法:
SELECT *, Row_Number() OVER (partition by 要按哪个重复的字段分组 ORDER BY 要按哪个字段排序 desc) as 别名 FROM t
where t.别名 = 1