select t2.material_id, t2.plan_unitprice from (select t.material_id, max(t.pro_date) pro_date from stor_storageaccount t) t1, stor_storageaccount t2 where t1.material_id = t2.material_id and t1.pro_date = t2.pro_date
select distinct t.material_id, first_value(t.plan_unitprice) over(partition by t.material_id order by t.pro_date desc) as plan_unitprice, first_value(t.pro_date) over(partition by t.material_id order by t.pro_date desc) as pro_date from stor_storageaccount t
SELECT TT.MATERIAL_ID, TT.PLAN_UNITPRICE FROM (SELECT T.MATERIAL_ID, T.PLAN_UNITPRICE, T.PRO_DATE, --分组排序标号 倒序排列 时间最大值就是为1的时候 ROW_NUMBER() OVER(PARTITION BY T.MATERIAL_ID ORDER BY T.PRO_DATE DESC) AS R_NUM FROM STOR_STORAGEACCOUNT T) TT WHERE TT.R_NUM = 1;
还是用下面这个写法吧,执行速度最快 select material_id, MAX(plan_unitprice) KEEP(DENSE_RANK LAST ORDER BY pro_date) from stor_storageaccount T group by material_id
select t2.material_id, t2.plan_unitprice
from (select t.material_id, max(t.pro_date) pro_date
from stor_storageaccount t) t1,
stor_storageaccount t2
where t1.material_id = t2.material_id
and t1.pro_date = t2.pro_date
first_value(t.plan_unitprice) over(partition by t.material_id order by t.pro_date desc) as plan_unitprice,
first_value(t.pro_date) over(partition by t.material_id order by t.pro_date desc) as pro_date
from stor_storageaccount t
FROM (SELECT T.MATERIAL_ID,
T.PLAN_UNITPRICE,
T.PRO_DATE,
--分组排序标号 倒序排列 时间最大值就是为1的时候
ROW_NUMBER() OVER(PARTITION BY T.MATERIAL_ID ORDER BY T.PRO_DATE DESC) AS R_NUM
FROM STOR_STORAGEACCOUNT T) TT
WHERE TT.R_NUM = 1;
select material_id,
MAX(plan_unitprice) KEEP(DENSE_RANK LAST ORDER BY pro_date)
from stor_storageaccount T
group by material_id