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

如上圖中數(shù)據(jù)顯示的那樣，現(xiàn)在要求 city_id 此表中不連接的的ID號(hào)。

0 ~ 11 之間丟失的數(shù)據(jù)是?? 1 ~ 10????????????????丟失數(shù)量 10

15 ~ 21 之間丟失的數(shù)據(jù)是 16 ~ 20????????????????丟失數(shù)量??5

....?????????????????????????????????????????????????????????? ....

顯示出來(lái)的內(nèi)容應(yīng)該是

lost_id????????????????????????????????lost_sum

1~10,16~20?????????????????????? 15

最后如下圖所示：

現(xiàn)在使用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 ,--上一個(gè)ID的值 ??????t.city_id c_id,?????????????????????????????????????????????????????????????????????????????? --當(dāng)前的ID值 ??????last_value(t.city_id)over(order by t.city_id rows between 1 preceding and 1 following )n_id,??--后一個(gè)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語(yǔ)句中 first_value(field) 函數(shù)，哪一列的值 over(order by t.city_id rows between 1 preceding and 1 following)??是 field 列當(dāng)中當(dāng)前行的 前一行的以后一行。 first_value(t.city_id)over(order by t.city_id rows between 1 preceding and 1 following) 是獲取city_id列當(dāng)前行的前一行的值。 last_value(t.city_id)over(order by t.city_id rows between 1 preceding and 1 following ) 獲取當(dāng)city_id列當(dāng)前行的下一行的值。

總結(jié)

以上是生活随笔為你收集整理的oracle11 不更新记录,oracle11g 使用first_value获取表中不连接的ID号及掉失记录数量...的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。