简单解释一下。
用oracle的分析函数,注意只有enterprise edition才支持。
rank () over (partition by spcode order by sale_date desc,bill_no desc)
partition by spcode 是以spcode分组,类似于group by.
order by sale_date desc就是以日期的降序排位。
加上bill_no desc是如果某种商品同一天内有4次纪录的话,避免取出3条以上的纪录。
select
bill_no,spcode,sale_date,sale_num
from
(select bill_no,spcode,sale_date,sale_num,
rank () over (partition by spcode order by sale_date desc,bill_no desc) as rank_date
from t1)
where
rank_date <=3;
SQL> select
2 bill_no,spcode,sale_date,sale_num
3 from
4 (select bill_no,spcode,sale_date,sale_num,(row_number() over (partition
by bill_no,spcode order by bill_no,spcode,sale_date)) rk from t1)
5 where
6 rk<=3;