需求是这样的:表SOURCE :oid(主键) cardasn(卡号) no(流水号)
1 001 1
2 001 3
3 002 1
4 002 3
5 002 5查询后希望得到的结构是 表RESULT :
oid(主键) cardasn(卡号) miss(丢失的流水号)
1 001 2
2 002 2
3 002 4 RESULT表的结构也可以变化,但只要能找出所有的断号就可以了
1 001 1
2 001 3
3 002 1
4 002 3
5 002 5查询后希望得到的结构是 表RESULT :
oid(主键) cardasn(卡号) miss(丢失的流水号)
1 001 2
2 002 2
3 002 4 RESULT表的结构也可以变化,但只要能找出所有的断号就可以了
from (
select oid ,no-row_number() over(order by no) sub
from aa
order by no
) a
where a.sub = 0这样取得没断号的纪录,按照这个思路,你自己写一下吧
基本思路:no排序并去重后,减去他的序号,差值比第一条的差值大的就是断号
当然,差值也需要取,不一定是0
感觉写个存储过程比较好
select * from
(select cardasn,no,ld from (select cardasn,no,lead(no) over(partition by cardasnorder by no) ld from cc) where ld is not null) t1,
(select level ll from dual connect by level < 100000) t2
where ll > no and ll < ld
from(select oid,cardasn,
row_number()(PARTITION BY cardasn ORDER BY cardasn) rn
from tb1) a
where not exists
(select * from tb b where b.cardasn=b.cardasn and b.rn = b.no)
能简单的解释一下吗? b.cardasn=b.cardasn and b.rn = b.no这里不明白
select cardasn , no+1 begin ,
lead(no,1,0) over(partition by cardasn order by no )-1 end,
lead(no,1,0) over(partition by cardasn order by no )- no-1 bb
from SOURCE ) where bb>0得出的结果集为:cardasn begin end bb(丢失流水的个数)
001 2 2 1
002 2 2 1
002 4 4 1
你试试这组测试数据
oid(主键) cardasn(卡号) no(流水号)
1 001 1
2 001 4
3 002 1
4 002 5
5 002 9
结果是几行?赫赫
[color=#FF00FF]a 表是把原来的表中流水号进行顺序排列rn,然后去除b表中已经有的流水号。剩下的就是断号。
b.cardasn=b.cardasn and b.rn = b.no 是a表和b表的关联条件[/color]