昨天遇见一道题:
在表A中有一字段 ID (integer), ID 值分别是1,2,3,5,6,7,10,…, 其中在3和5之间缺少一个4(starting gap number),7和10之间缺少8和9(只要求得到第一个缺少的数字8, 这叫starting gap number?),如何用sql得到所有的starting gap number值:4,8 ?
在表A中有一字段 ID (integer), ID 值分别是1,2,3,5,6,7,10,…, 其中在3和5之间缺少一个4(starting gap number),7和10之间缺少8和9(只要求得到第一个缺少的数字8, 这叫starting gap number?),如何用sql得到所有的starting gap number值:4,8 ?
select yy.aid_sign
from (
select tt.*,
lead(aid) over(order by aid),
case when aid+1 <> lead(aid) over(order by aid)
then aid+1
else -1
end as aid_sign
from tablename tt
)yy
where yy.aid_sign > 0;结果: AID_SIGN
----------
4
8
from
(select id as t1,LAG(id,1,0) OVER (ORDER BY id)+1 AS t2
from demo) t
where t.t1!=t.t2
tb a left join tb b
on a.id = (b.id-1)
where b.id is null
select max(rcn)+max(diff)+1 gra from
(select id,rownum rcn,id-rownum diff from hr.cc )
where diff<(select max(id-rownum) from hr.cc)
group by diff
order by 1
如果序列为:
"ID"
"2"
"3"
"5"
"6"
"8"
"10"
"11"
"12"
"13"
"17"
"18"
"22"
"24"
"25"
"30"
则你的结果为:
"ID"
"4"
"7"
"9"
"14"
"19"
"23"
"26"
"31"
按道理,这个"31"应该不算断点