Jak znaleźć "lukę" w uruchomieniu licznika z SQL?
Chciałbym znaleźć pierwszą "lukę" w kolumnie licznika w tabeli SQL. Na przykład, jeśli są wartości 1,2,4 i 5 chciałbym dowiedzieć się 3.
Mogę oczywiście ustawić wartości w kolejności i przejść przez to ręcznie, ale chciałbym wiedzieć, czy istnieje sposób, aby to zrobić w SQL.
Ponadto powinien to być dość standardowy SQL, pracujący z różnymi Dbmse.
14 answers
W MySQL
i 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
W 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
W 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
(działa wszędzie, najmniej efektywnie):
SELECT MIN(id) + 1
FROM mytable mo
WHERE NOT EXISTS
(
SELECT NULL
FROM mytable mi
WHERE mi.id = mo.id + 1
)
Systemy wspierające funkcje okien przesuwnych:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-10-10 11:04:03
Twoje odpowiedzi działają poprawnie, jeśli masz pierwszą wartość id = 1, w przeciwnym razie ta luka nie zostanie wykryta. Na przykład, jeśli twoje wartości ID tabeli wynoszą 3,4,5, Twoje zapytania zwrócą 6.
Zrobiłem coś takiego
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-09-12 13:33:23
Pierwsza rzecz, która przyszła mi do głowy. Nie wiem, czy to dobry pomysł, aby iść w ten sposób w ogóle, ale powinien działać. Załóżmy, że tabela To t
, a Kolumna to 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
Edit: to może być kleszcz szybciej (i krócej!):
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-08-21 14:40:15
To działa w SQL serverze - nie można tego przetestować w innych systemach, ale wydaje się to standardem...
SELECT MIN(t1.ID)+1 FROM mytable t1 WHERE NOT EXISTS (SELECT ID FROM mytable WHERE ID = (t1.ID + 1))
Możesz również dodać punkt wyjścia do klauzuli where...
SELECT MIN(t1.ID)+1 FROM mytable t1 WHERE NOT EXISTS (SELECT ID FROM mytable WHERE ID = (t1.ID + 1)) AND ID > 2000
Więc jeśli miałbyś 2000, 2001, 2002 i 2005, gdzie 2003 i 2004 nie istniały, to zwróciłby 2003.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-08-21 14:02:12
Nie ma naprawdę ekstremalnie standardowy sposób SQL, aby to zrobić, ale z jakąś formą klauzuli ograniczającej można zrobić
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)
Lub
SELECT TOP 1 `num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL
(SQL Server)
Lub
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-08-21 14:04:27
Wewnętrzne połączenie z widokiem lub sekwencją, która ma wszystkie możliwe wartości.
BRAK tabeli? Zróbcie stolik. Zawsze trzymam przy sobie głupi stół tylko po to.
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
Then,
select a.id from artificial_range a
where not exists ( select * from your_table b
where b.counter = a.id) ;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-08-21 14:02:58
My guess:
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-08-21 14:04:15
Dla PostgreSQL
Przykład wykorzystujący rekurencyjne zapytanie.
To może być przydatne, jeśli chcesz znaleźć lukę w określonym zakresie (będzie działać nawet jeśli tabela jest pusta, podczas gdy inne przykłady nie)
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-08-10 11:17:16
Ten opisuje wszystko, o czym do tej pory wspomniałem. Zawiera 0 jako punkt początkowy, który będzie domyślnie ustawiony, jeśli nie istnieją żadne wartości. Dodałem również odpowiednie lokalizacje dla innych części klucza wielowartościowego. To zostało przetestowane tylko na 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-07-02 17:12:13
select min([ColumnName]) from [TableName]
where [ColumnName]-1 not in (select [ColumnName] from [TableName])
and [ColumnName] <> (select min([ColumnName]) from [TableName])
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-07-16 10:51:33
Oto standardowe rozwiązanie SQL, które działa na wszystkich serwerach bazodanowych bez zmian:
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);
Zobacz w akcji dla;
- PL / SQL via Oracle ' s livesql ,
- MySQL via sqlfiddle ,
- PostgreSQL via sqlfiddle
- MS SQL via sqlfiddle
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-07-14 23:48:59
Działa również dla pustych tabel lub z wartościami negatywnymi. Po prostu testowane w 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-07-15 00:09:59
Jeśli używasz Firebird 3 jest to najbardziej eleganckie i proste:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-11-15 13:41:51
-- 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-08-06 02:37:36