趣味SQL 260216

#sql #postgresql

一級屠豬士 2026-02-16 08:40:30 ‧ 3354 瀏覽

分享至

table 裡使用sequence, 或是自動產生編號, 在實務上會因為rollback, 或是後來delete.就會產生缺號.
這次來找出缺號的區間.

測試table及資料:

create table c260216a (
  id int generated always as identity primary key
, txt text
);

insert into c260216a values
(default, 'a'), (default, 'b');

insert into c260216a(txt) values
('c'), ('d');

commit;

insert into c260216a(txt) values
('x'), ('y');

rollback;

insert into c260216a(txt) values
('e'), ('f');

commit;

insert into c260216a(txt)
select n::text
  from generate_series(9, 19) as n;

commit;

delete
  from c260216a
 where id in (10, 13, 14, 15);

commit;

select *
  from c260216a;

 id | txt
----+-----
  1 | a
  2 | b
  3 | c
  4 | d
  7 | e
  8 | f
  9 | 9
 11 | 11
 12 | 12
 16 | 16
 17 | 17
 18 | 18
 19 | 19
(13 筆資料)

預計結果:

 頭 | 尾
----+----
  5 |  6
 10 | 10
 13 | 15
(3 筆資料)

我是用這樣

with t1 as (
select id
     , lead(id) over(order by id) as next_id
  from c260216a
)
select id + 1 as 頭
     , next_id - 1 as 尾
  from t1
 where (next_id - id) > 1;

登入發表討論

熱門推薦

{{ item.channelVendor }} | {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2026-02-16 14:00:52

最佳解答

玩一下

create table c260216a (
  id int generated always as identity primary key
, txt text
);

insert into c260216a values
(default, 'a'), (default, 'b');

insert into c260216a(txt) values
('c'), ('d');

commit;

insert into c260216a(txt) values
('x'), ('y');

rollback;

insert into c260216a(txt) values
('e'), ('f');

commit;

insert into c260216a(txt)
select n::text
  from generate_series(9, 19) as n;

commit;

delete
  from c260216a
 where id in (10, 13, 14, 15);

commit;

select *
  from c260216a;

With X1 as (
Select id, Lead(id,1,0) Over (Order by id) AS id_temp
From c260216a)
  
Select (id + 1) as "From", (id_temp - 1) as "To"
From X1
Where (id_temp > 0) and (id_temp - id) > 1

Demo

回應
分享
檢舉

登入發表回應

jeremyjoung

iT邦新手 5 級 ‧ 2026-02-18 00:56:30

用LEFT JOIN 做k+1自我遞迴就可以輕鬆實現了而且不限SQL版本
LIMIT 對每個SQL不同改成 TOP 就好
會直接取出第一個洞或是最後新值
雖然不可避免的要FULL INDEX SEARCH 但至少比TABLE SCAN好太多了
這種中間插入概念由於缺乏流水鍵不重複性的天生交易保護記得要開鎖定

SELECT t1.k+1 newKey
FROM `tab` AS t1 -- 基底用索引，如果有專門的32bit流水號TAB 那也很好
LEFT JOIN `tab` AS t2 ON t1.k+1=t2.k -- 以索引為基礎 喊出下一個值
WHERE t2.k IS NULL -- 空號
ORDER BY t1.k LIMIT 1