[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)