趣味SQL 260216

#sql #postgresql

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

分享至

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 筆資料)

登入發表討論

熱門推薦

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

直播中

1 個回答

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