row_number使用一例---按时间排序取最新的一条记录

最近同事让帮忙写个SQL，要实现的功能如下：

对于商品相关信息表（有公司标识、商品ID、价格、时间等字段），已知 公司标识 和  商品ID 2个条件，要在一张表里  查出 这个商品ID的最近price字段的记录值。

同事发我的源SQL语句如下：

select b.prodno,b.prodname,b.prodspecification,b.packageunit,a.invbalqty,a.costprice,a.invbalamt,c.sellguidprice
,(select min(d.price) from tb_gos_purchase_purstockindet d where d.branchid=a.branchid and d.proid=a.prodid)
,(select price from (select price from tb_gos_purchase_purstockindet f where f.branchid=a.branchid and f.proid=a.prodid order by createtime desc) where rownum=1)
from tb_gos_account_o_braninven a 
inner join tb_common_prodmain b on b.branchid=a.branchid and b.prodid=a.prodid
inner join tb_common_procprice c on a.branchid=c.branchid and a.prodid=c.proid

执行后发现，标记红色字体  f.proid=a.prodid 处报错：  ORA-00904: "A"."PRODID": 标识符无效。

原因是，套用2个子查询，二级子查询识别不到外层查询中表的别名a。

从语句可看出难点在：要在tb_gos_purchase_purstockindet中找出某商品最近的price值，然后把对应商品的price记录结果与  tb_gos_account_o_braninven  通过prodid(或proid)商品ID字段join  选择出用户所需要的结果集信息。

对于在 tb_gos_purchase_purstockindet  中选择出商品最近的price记录值的SQL

修改如下（以指定一个商品ID为例）：

select * from (
select   f.proid,f.branchid, row_number()  over(partition by f.proid,f.branchid order by f.createtime desc ) as desk
,f.createtime,f.price from tb_gos_purchase_purstockindet f
where f.proid='SPH00000002'
)
where desk=1

最后将此部分合并到源SQL语句中，写成：

with f 
as( 
select * from (
select   f.proid,f.branchid, row_number()  over(partition by f.proid,f.branchid order by f.createtime desc ) as desk
,f.createtime,f.price from tb_gos_purchase_purstockindet f,tb_gos_account_o_braninven e
where f.proid=e.prodid
)
where desk=1)
select b.prodno,b.prodname,b.prodspecification,b.packageunit,a.invbalqty,a.costprice,a.invbalamt,c.sellguidprice
,(select min(d.price) from tb_gos_purchase_purstockindet d where d.branchid=a.branchid and d.proid=a.prodid)
,f.price
from tb_gos_account_o_braninven a 
inner join tb_common_prodmain b on b.branchid=a.branchid and b.prodid=a.prodid
inner join tb_common_procprice c on a.branchid=c.branchid and a.prodid=c.proid
inner join f on a.prodid=f.proid

 其中 row_number函数的功能是为查询出来的每一行记录生成一个序号。上例中在使用row_number函数是要使用over子句选择对某一列（createtime）进行排序，然后生成序号。