select * from (select row_number() over(partition by cardno order by times desc) rnum,
cardno,consumedate,cash,times from sells ) t
where t.rnum<6;
cardno,consumedate,cash,times from sells ) t
where t.rnum<6;
是一种分析函数,从oracle8.16版本开始支持这个写法的.
有兴趣的话可以参考一个expert one on one oracle 第十章.
(
select
row_number() over(partition by cardno order by consumedate desc) rnum,
cardno,consumedate,cash,times from sells
) t
where t.rnum<6;
例如:rank() ,dense_rank(),row_number 等等,有些非常实用。