怎么找失踪的号码？

Question

我有一张桌子上有一张号码表。我怎样才能找到表中丢失的数字？

例如：

numbers   id
 1         1
 5         2
 3         3
 6         4
 7         5
 8         6
 9         7
 20        8
 ....

我怎么才能找到丢失的号码？就像4一样，9和20之间没有数字。

我什么都没试过，但我想知道。

Answer 1

在postgresql中，您可以使用generate_series：

SELECT
  generate_series FROM GENERATE_SERIES(
    (select min(id) from numbers), (select max(id) from numbers)
  ) 
WHERE
  NOT EXISTS(SELECT id FROM numbers WHERE id = generate_series)

在mysql中，您需要自己生成意甲：生成意甲

Answer 2

在server中，我们可以使用系统表master..spt_values。

USE tempdb
GO

create table numbers(id int)
GO

insert into numbers
select 1
insert into numbers
select 1
insert into numbers
select 5
insert into numbers
select 2
insert into numbers
select 3
insert into numbers
select 3
GO

select number from master..spt_values
where type='p' and number <=(select max(id) from numbers)
and number not in (select id from numbers)

Answer 3

在MSSQL中，您可以执行以下操作：

--create a your table
CREATE TABLE ##seq
(id INT IDENTITY (1,1)
, txt VARCHAR(50))

--Build your dataset
INSERT INTO ##seq
        (  txt )
VALUES  ( 'boy'), ('girl'),('parent'),( 'boy'), ('girl'),('parent'),( 'boy'), ('girl'),('parent'),( 'boy'), ('girl'),('parent'),( 'boy'), ('girl'),('parent'),( 'boy'), ('girl'),('parent')

--remove your sequence ids to demonstrate
DELETE FROM ##seq WHERE id IN (2,5,7,8,9)

--Query your sequence for gaps 
SELECT (t1.id + 1) as gap_starts_at, 
       (SELECT MIN(t3.id) -1 FROM ##seq t3 WHERE t3.id > t1.id) as gap_ends_at
FROM ##seq t1
WHERE NOT EXISTS (SELECT t2.id FROM ##seq t2 WHERE t2.id = t1.id + 1)