我如何找到与SQL运行计数器一个“缺口”？(How do I find a “gap” in run

2019-06-18 17:25发布

站内文章 / 前端开发

31 0

爷的心禁止访问

女 | 书童

私信

我想找到一个SQL表中的计数器列的第一个“缺口”。例如，如果有值1,2,4和5，我想找出3。

当然，我可以得到的数值，以便和手工穿境而过，但我想知道是否会有办法做到这一点的SQL。

此外，它应该是相当标准的SQL，以不同的DBMS工作。

Answer 1:

在MySQL与PostgreSQL ：

SELECT  id + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )
ORDER BY
        id
LIMIT 1

在SQL Server ：

SELECT  TOP 1
        id + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )
ORDER BY
        id

在Oracle ：

SELECT  *
FROM    (
        SELECT  id + 1 AS gap
        FROM    mytable mo
        WHERE   NOT EXISTS
                (
                SELECT  NULL
                FROM    mytable mi 
                WHERE   mi.id = mo.id + 1
                )
        ORDER BY
                id
        )
WHERE   rownum = 1

ANSI （作品随处可见，效率最低的）：

SELECT  MIN(id) + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )

系统支持滑动窗口的功能：

SELECT  -- TOP 1
        -- Uncomment above for SQL Server 2012+
        previd
FROM    (
        SELECT  id,
                LAG(id) OVER (ORDER BY id) previd
        FROM    mytable
        ) q
WHERE   previd <> id - 1
ORDER BY
        id
-- LIMIT 1
-- Uncomment above for PostgreSQL

Answer 2:

你的回答都正常工作，如果你有一个第一值id = 1，否则这一差距将不会被检测到。举例来说，如果你的表ID值分别为3,4,5，你的查询将返回6。

我做了这样的事情

SELECT MIN(ID+1) FROM (
    SELECT 0 AS ID UNION ALL 
    SELECT  
        MIN(ID + 1)
    FROM    
        TableX) AS T1
WHERE
    ID+1 NOT IN (SELECT ID FROM TableX)

Answer 3:

即进入了我的脑海的第一件事情。不知道这是一个好主意，在所有走这条路，但应该工作。假设表是t和列是c ：

SELECT t1.c+1 AS gap FROM t as t1 LEFT OUTER JOIN t as t2 ON (t1.c+1=t2.c) WHERE t2.c IS NULL ORDER BY gap ASC LIMIT 1

编辑：这一个可能是蜱更快（更短！）：

SELECT min(t1.c)+1 AS gap FROM t as t1 LEFT OUTER JOIN t as t2 ON (t1.c+1=t2.c) WHERE t2.c IS NULL

Answer 4:

这适用于SQL服务器 - 在其他系统无法测试，但似乎标准...

SELECT MIN(t1.ID)+1 FROM mytable t1 WHERE NOT EXISTS (SELECT ID FROM mytable WHERE ID = (t1.ID + 1))

你也可以一个起点添加到where子句...

SELECT MIN(t1.ID)+1 FROM mytable t1 WHERE NOT EXISTS (SELECT ID FROM mytable WHERE ID = (t1.ID + 1)) AND ID > 2000

所以，如果你有2000年，2001年，2002年和2005年，其中2003年和2004年不存在，它会返回2003。

Answer 5:

是不是真的有一个非常标准SQL的方式来做到这一点，但也有一些限制格式条款，你可以做

SELECT `table`.`num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL
LIMIT 1

（MySQL和PostgreSQL）

要么

SELECT TOP 1 `num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL

（SQL服务器）

要么

SELECT `num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL
AND ROWNUM = 1

（Oracle）的

Answer 6:

内部连接到具有所有可能值的视图或序列。

没有表？一张桌子。我始终保持一个虚表周围只是这一点。

create table artificial_range( 
  id int not null primary key auto_increment, 
  name varchar( 20 ) null ) ;

-- or whatever your database requires for an auto increment column

insert into artificial_range( name ) values ( null )
-- create one row.

insert into artificial_range( name ) select name from artificial_range;
-- you now have two rows

insert into artificial_range( name ) select name from artificial_range;
-- you now have four rows

insert into artificial_range( name ) select name from artificial_range;
-- you now have eight rows

--etc.

insert into artificial_range( name ) select name from artificial_range;
-- you now have 1024 rows, with ids 1-1024

然后，

 select a.id from artificial_range a
 where not exists ( select * from your_table b
 where b.counter = a.id) ;

Answer 7:

我猜：

SELECT MIN(p1.field) + 1 as gap
FROM table1 AS p1  
INNER JOIN table1 as p3 ON (p1.field = p3.field + 2)
LEFT OUTER JOIN table1 AS p2 ON (p1.field = p2.field + 1)
WHERE p2.field is null;

Answer 8:

这一个占到目前为止提到的一切。它包括0为起点，其将默认为如果不存在的值也是如此。我还增加了多值密钥的其它部分的适当位置。这仅是SQL Server上进行测试。

select
    MIN(ID)
from (
    select
        0 ID
    union all
    select
        [YourIdColumn]+1
    from
        [YourTable]
    where
        --Filter the rest of your key--
    ) foo
left join
    [YourTable]
    on [YourIdColumn]=ID
    and --Filter the rest of your key--
where
    [YourIdColumn] is null

Answer 9:

对于PostgreSQL

这使得使用递归查询的例子。

如果你想找到一个特定范围的差距，这可能是有用的（它甚至会工作，如果表是空的，而其他的例子不会）

WITH    
    RECURSIVE a(id) AS (VALUES (1) UNION ALL SELECT id + 1 FROM a WHERE id < 100), -- range 1..100  
    b AS (SELECT id FROM my_table) -- your table ID list    
SELECT a.id -- find numbers from the range that do not exist in main table
FROM a
LEFT JOIN b ON b.id = a.id
WHERE b.id IS NULL
-- LIMIT 1 -- uncomment if only the first value is needed

Answer 10:

我写了这样做的快捷方式。不知道这是最有效的，但能够完成任务。请注意，它不会告诉你的差距，但是告诉你之前和后间隙的ID（记住的差距可能是多个值，因此，例如1,2,4,7,11等）

我使用的SQLite作为一个例子

如果这是你的表结构

create table sequential(id int not null, name varchar(10) null);

而这些都是你行

id|name
1|one
2|two
4|four
5|five
9|nine

该查询

select a.* from sequential a left join sequential b on a.id = b.id + 1 where b.id is null and a.id <> (select min(id) from sequential)
union
select a.* from sequential a left join sequential b on a.id = b.id - 1 where b.id is null and a.id <> (select max(id) from sequential);

https://gist.github.com/wkimeria/7787ffe84d1c54216f1b320996b17b7e

Answer 11:

select min([ColumnName]) from [TableName]
where [ColumnName]-1 not in (select [ColumnName] from [TableName])
and [ColumnName] <> (select min([ColumnName]) from [TableName])

Answer 12:

下面是标准，而无需更改所有数据库服务器上运行的SQL解决方案：

select min(counter + 1) FIRST_GAP
    from my_table a
    where not exists (select 'x' from my_table b where b.counter = a.counter + 1)
        and a.counter <> (select max(c.counter) from my_table c);

见行动;

PL / SQL通过Oracle的livesql ，
通过MySQL的sqlfiddle ，
通过PostgreSQL的sqlfiddle
经由MS SQL sqlfiddle

Answer 13:

它的工作原理为空表或底片值以及。只是在SQL Server 2012测试

 select min(n) from (
select  case when lead(i,1,0) over(order by i)>i+1 then i+1 else null end n from MyTable) w

Answer 14:

如果您使用火鸟3这是最优雅，最简单的：

select RowID
  from (
    select `ID_Column`, Row_Number() over(order by `ID_Column`) as RowID
      from `Your_Table`
        order by `ID_Column`)
    where `ID_Column` <> RowID
    rows 1

Answer 15:

            -- PUT THE TABLE NAME AND COLUMN NAME BELOW
            -- IN MY EXAMPLE, THE TABLE NAME IS = SHOW_GAPS AND COLUMN NAME IS = ID

            -- PUT THESE TWO VALUES AND EXECUTE THE QUERY

            DECLARE @TABLE_NAME VARCHAR(100) = 'SHOW_GAPS'
            DECLARE @COLUMN_NAME VARCHAR(100) = 'ID'


            DECLARE @SQL VARCHAR(MAX)
            SET @SQL = 
            'SELECT  TOP 1
                    '+@COLUMN_NAME+' + 1
            FROM    '+@TABLE_NAME+' mo
            WHERE   NOT EXISTS
                    (
                    SELECT  NULL
                    FROM    '+@TABLE_NAME+' mi 
                    WHERE   mi.'+@COLUMN_NAME+' = mo.'+@COLUMN_NAME+' + 1
                    )
            ORDER BY
                    '+@COLUMN_NAME

            -- SELECT @SQL

            DECLARE @MISSING_ID TABLE (ID INT)

            INSERT INTO @MISSING_ID
            EXEC (@SQL)

            --select * from @MISSING_ID

            declare @var_for_cursor int
            DECLARE @LOW INT
            DECLARE @HIGH INT
            DECLARE @FINAL_RANGE TABLE (LOWER_MISSING_RANGE INT, HIGHER_MISSING_RANGE INT)
            DECLARE IdentityGapCursor CURSOR FOR   
            select * from @MISSING_ID
            ORDER BY 1;  

            open IdentityGapCursor

            fetch next from IdentityGapCursor
            into @var_for_cursor

            WHILE @@FETCH_STATUS = 0  
            BEGIN
            SET @SQL = '
            DECLARE @LOW INT
            SELECT @LOW = MAX('+@COLUMN_NAME+') + 1 FROM '+@TABLE_NAME
                    +' WHERE '+@COLUMN_NAME+' < ' + cast( @var_for_cursor as VARCHAR(MAX))

            SET @SQL = @sql + '
            DECLARE @HIGH INT
            SELECT @HIGH = MIN('+@COLUMN_NAME+') - 1 FROM '+@TABLE_NAME
                    +' WHERE '+@COLUMN_NAME+' > ' + cast( @var_for_cursor as VARCHAR(MAX))

            SET @SQL = @sql + 'SELECT @LOW,@HIGH'

            INSERT INTO @FINAL_RANGE
             EXEC( @SQL)
            fetch next from IdentityGapCursor
            into @var_for_cursor
            END

            CLOSE IdentityGapCursor;  
            DEALLOCATE IdentityGapCursor;  

            SELECT ROW_NUMBER() OVER(ORDER BY LOWER_MISSING_RANGE) AS 'Gap Number',* FROM @FINAL_RANGE

Answer 16:

发现大部分的方法运行得非常，非常缓慢mysql 。这里是我的解决方案mysql < 8.0 。有接近尾声的空隙〜1秒完成测试的1M记录。不知道它是否适合其他SQL口味。

SELECT cardNumber - 1
FROM
    (SELECT @row_number := 0) as t,
    (
        SELECT (@row_number:=@row_number+1), cardNumber, cardNumber-@row_number AS diff
        FROM cards
        ORDER BY cardNumber
    ) as x
WHERE diff >= 1
LIMIT 0,1

I assume that sequence starts from `1`.

Answer 17:

以下解决方案：

提供的测试数据;
产生其他间隙内查询; 和
它工作在SQL Server 2012中。

编号排序的行顺序地在“ 与 ”子句，然后重复使用该结果两次内连接上的行数，但通过1偏移，以便该行之前的行之后，找ID的具有更大的间隙比比较1.以上要求，但更广泛的应用。

create table #ID ( id integer );

insert into #ID values (1),(2),    (4),(5),(6),(7),(8),    (12),(13),(14),(15);

with Source as (
    select
         row_number()over ( order by A.id ) as seq
        ,A.id                               as id
    from #ID as A WITH(NOLOCK)
)
Select top 1 gap_start from (
    Select 
         (J.id+1) as gap_start
        ,(K.id-1) as gap_end
    from       Source as J
    inner join Source as K
    on (J.seq+1) = K.seq
    where (J.id - (K.id-1)) <> 0
) as G

内查询产生：

gap_start   gap_end

3           3

9           11

外部查询生产：

gap_start

3

Answer 18:

如果您的计数器从1开始，并要生成序列的第一个数字（1）空时，这里是代码从甲骨文第一个答案有效校正片：

SELECT
  NVL(MIN(id + 1),1) AS gap
FROM
  mytable mo  
WHERE 1=1
  AND NOT EXISTS
      (
       SELECT  NULL
       FROM    mytable mi 
       WHERE   mi.id = mo.id + 1
      )
  AND EXISTS
     (
       SELECT  NULL
       FROM    mytable mi 
       WHERE   mi.id = 1
     )

文章来源: How do I find a “gap” in running counter with SQL?