SQL一对多取子表最新记录的所有字段(ROW_NUMBER()OVER()函数的应用)

ROW_NUMBER()OVER()

参数1：分组字段 PARTITION BY   ..，..，....  可选

参数2：排序字段 ORDER BY .. DESC  必须

实例:

根据ConfirmDate 排序,PayApplyId 分组

SELECT * FROM PayApplyConfirmRecord  
SELECT ROW_NUMBER()OVER(PARTITION BY PayApplyId ORDER BY ConfirmDate DESC)  rowId,* from dbo.PayApplyConfirmRecord

结果:

实际应用场景

支付申请表PayApply关联审核表PayApplyConfirmRecord 关系为一对多,查询所有支付申请记录以及最新的审核记录

SELECT b.*,a.* FROM dbo.PayApply a
LEFT JOIN ( select * from (select ROW_NUMBER()over(PARTITION BY PayApplyId order by ConfirmDate desc) rowId,* from dbo.PayApplyConfirmRecord)a where a.rowId=1 ) b ON a.Id=b.PayApplyId

结果:

 

当然,这个函数也可以用于去重和分页,上面只是一个我开发中遇到的实际需要查询的数据