select t1.* from
(select rownum id,入金NO,行NO from
(select 入金NO,行NO from tbname order by 行NO) t) t1,
(select min(id) id,行NO from (select rownum id,入金NO,行NO from
(select 入金NO,行NO from tbname order by 行NO)) group by 行NO) t2
where
t1.行NO=t2.行NO(+) and t1.id-t2.id<=2;
(select rownum id,入金NO,行NO from
(select 入金NO,行NO from tbname order by 行NO) t) t1,
(select min(id) id,行NO from (select rownum id,入金NO,行NO from
(select 入金NO,行NO from tbname order by 行NO)) group by 行NO) t2
where
t1.行NO=t2.行NO(+) and t1.id-t2.id<=2;
ex)需要某类业务的最后n条记录。
ex)详细些
客户a,b...z公司的最后两次业务 信息显示------------------------------------------------------------------------------
请给出实现的脚本?
(select a.*,rank() over(partition by 行NO order by substr(入金NO,2)) rk from tabname)
where rk<=2
and t1.id-t2.id<=2;
t1里,没有T1.ID错误select * from
(select a.*,rank() over(partition by 行NO order by substr(入金NO,2)) rk from tabname a)
where rk<=2
结果正确
可以解释rank() ,over, partition 的用法吗?
rank() over(partition by 行NO order by substr(入金NO,2)) rk
即按照partition指定的列分组,
并按照order by 指定的列排序后的结果给出名次(顺序号)。不指定partition by就按全部记录排序。这里不用rank() 用row_number()也可以。
100 1 1 1
35 2 2 2
23 3 3 3
8 4 4 4
8 4 4 5
6 5 6 6