oracle11g 使用first_value获取表中不连接的ID号及掉失记录数量

 

[select * from city t order by t.city_id]

如上图中数据显示的那样，现在要求 city_id 此表中不连接的的ID号。
  0 ~ 11 之间丢失的数据是   1 ~ 10                丢失数量 10
15 ~ 21 之间丢失的数据是 16 ~ 20                丢失数量  5
....                                                           ....
显示出来的内容应该是
lost_id                                lost_sum
1~10,16~20                       15

最后如下图所示：

 

 

 

现在使用sql 显示上面的信息
select wm_concat(preced_id||'~'||last_id)lose_id,sum(last_id -preced_id + 1 ) lose_sum from (
  select p_id +1 preced_id,c_id -1 last_id,a.city_name from (
    select
      first_value(t.city_id)over(order by t.city_id rows between 1 preceding and 1 following) p_id ,--上一个ID的值
      t.city_id c_id,                                                                               --当前的ID值
      last_value(t.city_id)over(order by t.city_id rows between 1 preceding and 1 following )n_id,  --后一个ID的值
      t.city_parent_id,t.city_name
    from city t
    --where t.city_id < 100
  ) a
  where a.p_id + 1 <> a.c_id and c_id -1 > 0
)

在上面的SQL语句中 first_value(field) 函数，哪一列的值
over(order by t.city_id rows between 1 preceding and 1 following)  是 field 列当中当前行的 前一行的以后一行。
first_value(t.city_id)over(order by t.city_id rows between 1 preceding and 1 following)
是获取city_id列当前行的前一行的值。
last_value(t.city_id)over(order by t.city_id rows between 1 preceding and 1 following )
获取当city_id列当前行的下一行的值。