Sql как найти пробел

select *
from [sometable]
where CHARINDEX(' ', [somecolumn]) > 0

2

Я хотел бы найти первый «пробел» в столбце счетчика в таблице SQL. Например, если есть значения 1,2,4 и 5, я бы хотел узнать 3.

Я, конечно, могу привести значения в порядок и просмотреть их вручную, но я хотел бы знать, есть ли способ сделать это в SQL.

Кроме того, это должен быть вполне стандартный SQL, работающий с разными СУБД.

person
Touko
  
schedule
21.08.2009
  
source
источник

В 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

In 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

In 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

person
Quassnoi
  
schedule
21.08.2009

Все ваши ответы работают нормально, если у вас есть первое значение id = 1, иначе этот пробел не будет обнаружен. Например, если значения идентификатора вашей таблицы 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) 

person
Ruben
  
schedule
12.09.2012

На самом деле нет чрезвычайно стандартного способа 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)

or

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

(SQL-сервер)

or

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

(Оракул)

person
chaos
  
schedule
21.08.2009

Первое, что пришло в голову. Не уверен, что это вообще хорошая идея, но должно сработать. Предположим, что таблица 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

person
Michael Krelin – hacker
  
schedule
21.08.2009

Это работает в SQL Server – не может проверить это в других системах, но кажется стандартным …

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.

person
Mayo
  
schedule
21.08.2009

Следующее решение:

• предоставляет тестовые данные;
• внутренний запрос, который производит другие пробелы; и
• он работает в SQL Server 2012.

Последовательно пронумеровывает упорядоченные строки в предложении «с», а затем повторно использует результат дважды с внутренним соединением по номеру строки, но смещением на 1, чтобы сравнить предыдущую строку со строкой после, глядя для идентификаторов с пробелом больше 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

person
wwmbes
  
schedule
07.02.2019

Внутреннее присоединение к представлению или последовательности, имеющей все возможные значения.

Нет стола? Сделайте стол. Я всегда держу под рукой пустышку только для этого.

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) ;

person
tpdi
  
schedule
21.08.2009

Это объясняет все, о чем говорилось до сих пор. Он включает 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

person
Carter Medlin
  
schedule
02.07.2013

Для 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

person
AlexM
  
schedule
10.08.2016

Моя догадка:

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;

person
Leonel Martins
  
schedule
21.08.2009

Я написал быстрый способ сделать это. Не уверен, что это самый эффективный вариант, но выполняет свою работу. Обратите внимание, что он не сообщает вам пробел, но сообщает идентификатор до и после пробела (имейте в виду, что пробел может быть несколькими значениями, например 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

person
William Kimeria
  
schedule
25.03.2019

Вот стандартное решение 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 lifeql,
• MySQL через sqlfiddle,
• PostgreSQL через sqlfiddle
• MS Sql через sqlfiddle

person
Mehmet Kaplan
  
schedule
14.07.2017

Это работает также для пустых таблиц или с отрицательными значениями. Только что протестировано в 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

person
Horaciux
  
schedule
15.07.2017

Если вы используете Firebird 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

person
Rosen Nikolov
  
schedule
15.11.2017

Было обнаружено, что большинство подходов выполняются очень, очень медленно в mysql. Вот мое решение для mysql < 8.0. Проверено на 1 млн записей с разрывом в конце ~ 1 секунда до конца. Не уверен, подходит ли он другим вариантам SQL.

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

I assume that sequence starts from `1`.

person
Max Ivanov
  
schedule
10.01.2019

Если ваш счетчик начинается с 1 и вы хотите сгенерировать первый номер последовательности (1), когда он пуст, вот исправленный фрагмент кода из первого ответа, действительного для Oracle:

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
     )  

person
kozo
  
schedule
15.03.2019

Если числа в столбце являются положительными целыми числами (начиная с 1), то вот как легко решить эту проблему. (при условии, что ID – это имя вашего столбца)

    SELECT TEMP.ID 
    FROM (SELECT ROW_NUMBER() OVER () AS NUM FROM 'TABLE-NAME') AS TEMP 
    WHERE ID NOT IN (SELECT ID FROM 'TABLE-NAME')
    ORDER BY 1 ASC LIMIT 1

person
Abrhalei
  
schedule
11.10.2019

Вот альтернатива, чтобы показать диапазон всех возможных значений зазора портативным и более компактным способом:

Предположим, ваша схема таблицы выглядит так:

> SELECT id FROM your_table;
+-----+
| id  |
+-----+
|  90 |
| 103 |
| 104 |
| 118 |
| 119 |
| 120 |
| 121 |
| 161 |
| 162 |
| 163 |
| 185 |
+-----+

Чтобы получить диапазоны всех возможных значений зазора, у вас есть следующий запрос:

• В подзапросе перечисляются пары идентификаторов, в каждой из которых столбец lowerbound меньше столбца upperbound, затем используются GROUP BY и MIN(m2.id), чтобы уменьшить количество бесполезных записей.
• Внешний запрос дополнительно удаляет записи, в которых lowerbound равно upperbound - 1.
• Мой запрос (явно) не выводит 2 записи (YOUR_MIN_ID_VALUE, 89) и (186, YOUR_MAX_ID_VALUE) на обоих концах, что неявно означает, что любое число в обоих диапазонах до сих пор не использовалось в your_table.

> SELECT  m3.lowerbound + 1, m3.upperbound - 1 FROM
  (
    SELECT m1.id as lowerbound, MIN(m2.id) as upperbound FROM
    your_table m1 INNER JOIN your_table
    AS m2 ON m1.id < m2.id GROUP BY m1.id
  )
  m3 WHERE m3.lowerbound < m3.upperbound - 1;

+-------------------+-------------------+
| m3.lowerbound + 1 | m3.upperbound - 1 |
+-------------------+-------------------+
|                91 |               102 |
|               105 |               117 |
|               122 |               160 |
|               164 |               184 |
+-------------------+-------------------+

person
Han
  
schedule
17.03.2021