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

最新推荐文章于 2025-01-04 17:45:59 发布

xiaotuni

最新推荐文章于 2025-01-04 17:45:59 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库方面文章标签： oracle11g sql c

本文链接：https://blog.youkuaiyun.com/xiaotuni/article/details/5884051

数据库方面专栏收录该内容

30 篇文章

订阅专栏

本文介绍了如何使用Oracle11g的SQL查询来找出表格中city_id不连续的区间及其缺失数量。通过first_value和last_value函数结合窗口函数，可以有效地识别出数据丢失的ID范围并计算总数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[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列当前行的下一行的值。