下一个可用值 - PostgreSQL

Question

我有一些三位数字的客户编号，从“001”到“999”。有时会出现空缺，可以重新使用。我正在尝试填补这些空缺。因此，我正在寻找一种找到第一个可用空缺的方法。

CREATE TABLE co
( co_clno varchar(3));

INSERT INTO co
VALUES 
('001'),
('002'),
('003'),
('005'),
('006'),
('007'),
('008');

这里可用的间隔是“004”。 我尝试过首先创建一个可用数字的列表，但没有成功：

WITH numbers AS
     (SELECT to_char(generate_series(1,9),'000') num)
SELECT num FROM numbers 
     WHERE num NOT IN(SELECT co_clno FROM co)

最终代码应该是这样的：

WITH numbers AS
     (SELECT to_char(generate_series(1,9),'000') num)
SELECT min(num) FROM numbers 
     WHERE num NOT IN(SELECT co_clno FROM co)

SQLFiddle： http://sqlfiddle.com/#!15/1e48d/1

万分感谢任何线索。

- sibert

Answer 1

select substring(to_char(n,'000') from 2) as num
from generate_series(1,9) gs(n)

except

select co_clno
from co

order by 1
limit 1

- Clodoaldo Neto

---

谢谢！简单干净。它像预期的那样工作。 - sibert

Answer 2

您可以使用lead()函数查找间隙的起始位置：

select n.*
from (select n.*, lead(co_clno) over (order by co_clno) as next_num
      from co n
     ) n
where next_num is null or 
      n. co_clno::int <> (next_num::int) - 1
order by co_clno
limit 1;

您可以使用以下代码获取下一个值：

select to_char((n. co_clno::int) + 1, '000')
from (select n.*, lead(co_clno) over (order by co_clno) as next_num
      from co n
     ) n
where next_num is null or 
      n. co_clno::int <> next_num::int
order by co_clno
limit 1;

这样做的唯一问题是，如果缺少第一个值，它将无法获取。嗯……

select (case when first_num <> '001' then '001'
             else min(to_char((n. co_clno::int) + 1, '000'))
        end)
from (select n.*, lead(co_clno) over (order by co_clno) as next_num,
             min(co_clno) over () as first_num
      from co n
     ) n
where next_num is null or 
      n. co_clno::int <> (next_num::int) - 1
group by first_num;

- Gordon Linoff

---

它将找到“009”，而不是第一个可用的数字“004”。http://sqlfiddle.com/#!15/1e48d/10 - sibert

第二个查询将返回可用的“002”。http://sqlfiddle.com/#!15/1e48d/11 - sibert

@sibert . . . 这个查询很好用。你的 SQL Fiddle 是错误的：http://sqlfiddle.com/#!15/1e48d/10。我不知道你的 CTE 是从哪里来的。 - Gordon Linoff

没有使用公共表达式（AFAIK）：错误：关系“numbers”不存在？ - sibert

@sibert...我只是使用了你的select中的命名约定...我没有意识到表有其他名称。 - Gordon Linoff

Answer 3

@GordonLinoff的想法是正确的，但我不确定实现方式。

所以这是我的查询版本：

with 
  t(n) as (
    values
      ('001'),
      ('002'),
      ('005'),
      ('003'),
      ('006'),
      ('009'),
      ('010'),
      ('012')),
  t1 as (
    select
      n,
      lag(n, -1) over (order by n)::int - n::int - 1 as cnt
    from t)
select 
  to_char(generate_series(n::int+1, n::int+cnt), '000') as gap
from
  t1
where 
  cnt > 0;

结果是：

 gap  
------
  004
  007
  008
  011
(4 rows)

为了解决缺少第一个值的问题，只需使用：

select '000' union all <your data>

- Abelisto