我如何找到“缺口"?在与SQL运行计数器? [英] How do I find a "gap" in running counter with SQL?

问题描述

我想在SQL表的计数器列中找到第一个间隙".例如，如果有值1,2,4和5，我想找出3.

I'd like to find the first "gap" in a counter column in an SQL table. For example, if there are values 1,2,4 and 5 I'd like to find out 3.

我当然可以按顺序获取值并手动进行操作，但是我想知道是否可以使用SQL进行处理.

I can of course get the values in order and go through it manually, but I'd like to know if there would be a way to do it in SQL.

此外，它应该是非常标准的SQL，可以与不同的DBMS一起使用.

In addition, it should be quite standard SQL, working with different DBMSes.

推荐答案

在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(无处不在，效率最低):

ANSI (works everywhere, least efficient):

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

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

Systems supporting sliding window functions:

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

这篇关于我如何找到“缺口"?在与SQL运行计数器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！