복붙노트

[SQL] SQL은 : 테이블에 ID를 누락을 발견 한

SQL

SQL은 : 테이블에 ID를 누락을 발견 한

나는 독특한 자동 증가 기본 키 테이블이 있습니다. 시간이 지남에 따라 항목이 테이블에서 삭제, 그래서이 필드의 값에 "구멍"이있다 할 수있다. 예를 들어, 다음과 같은 테이블 데이터가 될 수있다 :

 ID  | Value    | More fields...
---------------------------------
 2   | Cat      | ... 
 3   | Fish     | ...
 6   | Dog      | ...
 7   | Aardvark | ...
 9   | Owl      | ...
 10  | Pig      | ...
 11  | Badger   | ...
 15  | Mongoose | ...
 19  | Ferret   | ...

나는 테이블에서 누락 ID의 목록을 반환하는 쿼리에 관심이 있어요. 위의 데이터의 경우, 예상 결과는 다음과 같습니다

 ID 
----
 1
 4
 5
 8
 12
 13
 14
 16
 17
 18

노트:

위의 요구 사항의 단점은 목록 ID 19 이후에 만들어진 그이 삭제 된 ID를 반환하지 것입니다. 내가 만든 최대 ID를 유지하기 때문에 나는 현재 코드에서이 사건을 해결하고있다. 쿼리 매개 변수 MaxID로 가지고 가고, 또한 현재 최대 및 MaxID 사이에 그 ID를 반환 할 경우, 그 (확실히 아닌 필수) 멋진 "보너스"가 될 것입니다.

저는 현재 MySQL과 일하고 있어요,하지만 난 모두에 맞게 쿼리를 싶습니다 그래서, SQL 서버로 이동하는 것이 좋습니다. 당신이 SQLite는 실행할 수 없습니다 아무것도를 사용하는 경우 또한, 덕분에 그것을 언급하시기 바랍니다.

해결법

  1. ==============================

    1.이 질문은 자주오고, 슬프게도, 가장 일반적인 (그리고 대부분의 휴대용) 대답이 있어야 ID를 유지하기 위해 임시 테이블을 생성하고, 왼쪽에 가입하는 것입니다. 구문은 MySQL과 SQL Server 간의 매우 유사하다. 유일한 차이는 임시 테이블 구문입니다.

    이 질문은 자주오고, 슬프게도, 가장 일반적인 (그리고 대부분의 휴대용) 대답이 있어야 ID를 유지하기 위해 임시 테이블을 생성하고, 왼쪽에 가입하는 것입니다. 구문은 MySQL과 SQL Server 간의 매우 유사하다. 유일한 차이는 임시 테이블 구문입니다.

    MySQL은 :

    declare @id int
declare @maxid int

set @id = 1
select @maxid = max(id) from tbl

create temporary table IDSeq
(
    id int
)

while @id < @maxid
begin
    insert into IDSeq values(@id)

    set @id = @id + 1
end

select 
    s.id 
from 
    idseq s 
    left join tbl t on 
        s.id = t.id 
 where t.id is null

 drop table IDSeq

    SQL 서버의 경우 :

    declare @id int
declare @maxid int

set @id = 1
select @maxid = max(id) from tbl

create table #IDSeq
(
    id int
)

while @id < @maxid --whatever you max is
begin
    insert into #IDSeq values(@id)

    set @id = @id + 1
end

select 
    s.id 
from 
    #idseq s 
    left join tbl t on 
        s.id = t.id 
 where t.id is null

 drop table #IDSeq
  2. ==============================

    2.여기에 SQL 서버에 대한 쿼리는 다음과 같습니다

    여기에 SQL 서버에 대한 쿼리는 다음과 같습니다

    ;WITH Missing (missnum, maxid)
AS
(
 SELECT 1 AS missnum, (select max(id) from @TT)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN @TT tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0);

    이 도움이됩니다 바랍니다.

  3. ==============================

    3.이것은 단지 SQLite는이 같은 질문을 검색 할 때 내가 찾은 대답했다 나는 SQLite는 대한 해결책을 찾기 위해 희망이 페이지에 착륙했습니다.

    이것은 단지 SQLite는이 같은 질문을 검색 할 때 내가 찾은 대답했다 나는 SQLite는 대한 해결책을 찾기 위해 희망이 페이지에 착륙했습니다.

    내가 찾은 마지막 해결책은 여기이 문서에서했다 중동 블로그 플로트 - SQLite는 대답을

    그것은 다른 사람 밖으로 희망이 도움 :-)

    간단한 해결책 것 :

    SELECT DISTINCT id +1
FROM mytable
WHERE id + 1 NOT IN (SELECT DISTINCT id FROM mytable);

    천재.

  4. ==============================

    4.나는 그것이 오래된 질문과 이미 허용 답을 가지고 알고 하지만 임시 테이블을 사용하는 것은 정말 필요가 없습니다. (더블 게시물에 대한 죄송) 형식을 수정했습니다.

    나는 그것이 오래된 질문과 이미 허용 답을 가지고 알고 하지만 임시 테이블을 사용하는 것은 정말 필요가 없습니다. (더블 게시물에 대한 죄송) 형식을 수정했습니다.

    DECLARE @TEST_ID integer, @LAST_ID integer, @ID integer

SET @TEST_ID = 1 -- start compare with this ID 
SET @LAST_ID = 100 -- end compare with this ID

WHILE @TEST_ID <= @LAST_ID 
BEGIN 
  SELECT @ID = (SELECT <column> FROM <table> WHERE <column> = @TEST_ID) 
  IF @ID IS NULL 
  BEGIN 
    PRINT 'Missing ID: ' + CAST(@TEST_ID AS VARCHAR(10)) 
  END 
  SET @TEST_ID = @TEST_ID + 1 
END
  5. ==============================

    5.PostgreSQL은 전용, 여기에 다른 답변에서 영감을.

    PostgreSQL은 전용, 여기에 다른 답변에서 영감을.

    SELECT all_ids AS missing_ids
FROM generate_series((SELECT MIN(id) FROM your_table), (SELECT MAX(id) FROM your_table)) all_ids
EXCEPT 
SELECT id FROM your_table
  6. ==============================

    6.이것은 오라클 유일한 솔루션입니다. 그것은 전체 문제를 해결하지 않지만, 오라클을 사용하고있을 수 있습니다 다른 사람을 위해 여기 남아 있습니다.

    이것은 오라클 유일한 솔루션입니다. 그것은 전체 문제를 해결하지 않지만, 오라클을 사용하고있을 수 있습니다 다른 사람을 위해 여기 남아 있습니다.

    select level id           -- generate 1 .. 19
from dual
connect by level <= 19

minus                     -- remove from that set

select id                 -- everything that is currently in the 
from table                -- actual table
  7. ==============================

    7.단일 쿼리는 누락 된 ID를 찾을 수 있습니다 ..

    단일 쿼리는 누락 된 ID를 찾을 수 있습니다 ..

    SELECT distinct number

FROM master..spt_values

WHERE number BETWEEN 1 and (SELECT max(id) FROM MyTable)

AND number NOT IN (SELECT id FROM MyTable)
  8. ==============================

    8.

    DECLARE @MaxID INT = (SELECT MAX(ID) FROM TABLE1)
SELECT SeqID AS MissingSeqID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY column_id) SeqID from sys.columns) LkUp
LEFT JOIN dbo.TABLE1 t ON t.ID = LkUp.SeqID
WHERE t.ID is null and SeqID < @MaxID
  9. ==============================

    9.업데이트 :이 방법은 너무 오래 내가 텍스트 파일의 차이를 찾기 위해 리눅스 명령을 쓴 있도록 방법을했다. 너무 처음과 같이 텍스트 파일에 대한 모든 아이디의 덤프 역순으로 그렇게;

    업데이트 :이 방법은 너무 오래 내가 텍스트 파일의 차이를 찾기 위해 리눅스 명령을 쓴 있도록 방법을했다. 너무 처음과 같이 텍스트 파일에 대한 모든 아이디의 덤프 역순으로 그렇게;

    nohup mysql --password=xx -e 'select id from tablename order by id desc' databasename > /home/ids.txt &

    처음과 마지막 두 줄은 걸린 시간을 추적하기 위해 단지이다. (틱) 1.5million ID는 느린 서버 및 그의 날 57sec했다. 난의 최대 ID를 설정하고 그것을 가지고있다.

    T="$(date +%s)"; \
i=1574115; \
while read line; do \
    if  [[ "$line" != "$i" ]] ; then \
        if [[ $i -lt 1 ]] ; then break; fi; \
        if  [[ $line -gt 1 ]] ; then \
            missingsequenceend=$(( $line + 1 )); \
            minusstr="-"; \
            missingsequence="$missingsequenceend$minusstr$i"; \
            expectnext=$(( $line - 1 )); \
            i=$expectnext; \
            echo -e "$missingsequence"; \
        fi; \
    else \
        i=$(( $i - 1 )); \
    fi; \
done \
< /home/ids.txt; \
T="$(($(date +%s)-T))"; \
echo "Time in seconds: ${T}"

    출력 예 :

    1494505-1494507
47566-47572
Time in seconds: 57

    또한, 나는 에릭의 대답에서 코드 구문 오류를 얻었으나, 구분 기호를 변경 적절한 장소에서 세미콜론을 사용하고 절차에 저장 한 후, 그것을 작동합니다.

    당신이 적절한 최대 ID, 데이터베이스 이름과 테이블 이름을 (그것이 선택 쿼리에서의)를 설정해야합니다. 프로 시저의 이름을 변경하려는 경우에, 3 곳에서 변경합니다.

    use dbname;
drop procedure if exists dorepeat;
delimiter #
CREATE PROCEDURE dorepeat()
BEGIN
set @id = 1;
set @maxid = 1573736;
drop table if exists IDSeq;
create temporary table IDSeq
(
    id int
);

WHILE @id < @maxid DO
    insert into IDSeq values(@id);
    set @id = @id + 1;
END WHILE;

select 
    s.id 
from 
    IDSeq s 
    left join tablename t on 
        s.id = t.id 
 where t.id is null;

drop table if exists IDSeq;

END#
delimiter ;
CALL dorepeat;

    또한이 쿼리 elwhere를 발견,하지만 난 그것을 테스트하지 않았습니다.

    SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM tablename AS a, tablename AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)
  10. ==============================

    10.MySQL은 TRY

    MySQL은 TRY

    DELIMITER ||
DROP PROCEDURE IF EXISTS proc_missing ||
CREATE PROCEDURE proc_missing()
BEGIN 
SET @minID = (SELECT MIN(`id`) FROM `tbl_name` WHERE `user_id`=13);
SET @maxID = (SELECT MAX(`id`) FROM `tbl_name` WHERE `user_id`=13);
REPEAT 
    SET @tableID = (SELECT `id` FROM `tbl_name` WHERE `id` = @minID);
    IF (@tableID IS NULL) THEN
        INSERT INTO temp_missing SET `missing_id` = @tableID;
    END IF;
    SET @minID = @minID + 1;
UNTIL(@minID <= @maxID)
END REPEAT;
END ||
DELIMITER ;
  11. ==============================

    11.이 쿼리를 사용해보십시오. (어떤 테이블 이름을 사용하고있는 TABLE_NAME을 교체하십시오) :이 단일 쿼리 숫자 누락 얻을 충분하다

    이 쿼리를 사용해보십시오. (어떤 테이블 이름을 사용하고있는 TABLE_NAME을 교체하십시오) :이 단일 쿼리 숫자 누락 얻을 충분하다

    select sno as missing from(SELECT @row := @row + 1 as sno FROM 
(select 0 union all select 1 union all select 3 union all select 4 union all 
select 5 union all select 6 union all select 6 union all select 7 union all 
select 8 union all select 9) t,(select 0 union all select 1 union all select 3 
union all select 4 union all select 5 union all select 6 union all select 6 
union all select 7 union all select 8 union all select 9) t2,(select 0 
union all select 1 union all select 3 union all select 4 union all select 5 
union all select 6 union all select 6 union all select 7 union all select 8 
union all select 9) t3, (select 0 union all select 1 union all select 3 union 
all select 4 union all select 5 union all select 6 union all select 6 union all 
select 7 union all select 8 union all select 9) t4, 
(SELECT @row:=0) as b where @row<1000) as a where a.sno  not in 
  (select distinct b.no from 
(select b.*,if(@mn=0,@mn:=b.no,@mn) as min,(@mx:=b.no) as max from 
  (select ID as no from TABLE_NAME as a) as b,
        (select @mn:=0,@mx:=0) as x order by no) as b) and 
         a.sno between @mn and @mx;
  12. ==============================

    12.며칠 전, 나는 생산 보고서 작업을했고 몇 가지 숫자가 누락 발견했다. 내가 조사를 목적으로 누락 된 모든 번호 목록을 찾을 요청 그래서 누락 된 숫자는 매우 중요하다. 나는 샘플 테이블에없는 번호 / ID를 찾을 수있는 스크립트를 포함, 전체 데모와 함께, 여기에 블로그 항목을 게시했다.

    며칠 전, 나는 생산 보고서 작업을했고 몇 가지 숫자가 누락 발견했다. 내가 조사를 목적으로 누락 된 모든 번호 목록을 찾을 요청 그래서 누락 된 숫자는 매우 중요하다. 나는 샘플 테이블에없는 번호 / ID를 찾을 수있는 스크립트를 포함, 전체 데모와 함께, 여기에 블로그 항목을 게시했다.

    내가 여기에 포함되지 않도록 제안 스크립트는 매우 깁니다. 여기에 사용되는 기본 단계는 다음과 같습니다

  13. ==============================

    13.(: 테이블의 이름으로 YOURTABLE 대체)이이 것 같습니다 오라클 버전 (폴 비린에서)를 SQL CTE를 변환 :

    (: 테이블의 이름으로 YOURTABLE 대체)이이 것 같습니다 오라클 버전 (폴 비린에서)를 SQL CTE를 변환 :

    WITH Missing (missnum,maxid) as (
  SELECT 1 missnum, (select max(id) from :YOURTABLE) maxid from dual
  UNION ALL
  SELECT m.missnum + 1,m.maxid 
  FROM Missing m
  WHERE m.missnum < m.maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN :YOURTABLE tt on tt.id = Missing.missnum
WHERE tt.id is NULL
  14. ==============================

    14.PaulSvirin의 대답 @ 사용하여, 나는이 null로 누락 된 기록을 포함 내 테이블의 모든 데이터를 표시하려면 UNION으로 확대했습니다.

    PaulSvirin의 대답 @ 사용하여, 나는이 null로 누락 된 기록을 포함 내 테이블의 모든 데이터를 표시하려면 UNION으로 확대했습니다.

    WITH Missing(missnum, maxid) AS
          (SELECT (SELECT MIN(tmMIN.TETmeetingID)
                   FROM tblTETMeeting AS tmMIN)
                      AS missnum,
                  (SELECT MAX(tmMAX.TETmeetingID)
                   FROM tblTETMeeting AS tmMAX)
                      AS maxid
           UNION ALL
           SELECT missnum + 1, maxid
           FROM Missing
           WHERE missnum < maxid)
SELECT missnum AS TETmeetingID,
       tt.DateID,
       tt.WeekNo,
       tt.TETID
FROM Missing LEFT JOIN tblTETMeeting tt ON tt.TETmeetingID = Missing.missnum
WHERE tt.TETmeetingID IS NULL
UNION
SELECT tt.TETmeetingID,
       tt.DateID,
       tt.WeekNo,
       tt.TETID
FROM tblTETMeeting AS tt
OPTION ( MAXRECURSION 0 )

    일이 좋아요!

    TETmeetingID    DateID  WeekNo  TETID
29  3063    21  1
30  null    null    null
31  null    null    null
32  null    null    null
33  null    null    null
34  3070    22  1
35  3073    23  1
  15. ==============================

    15.

    SELECT DISTINCT id -1
FROM users
WHERE id != 1 AND id - 1 NOT IN (SELECT DISTINCT id FROM users)

    설명 : (ID - 1) ..... 테이블의 이전 ID 존재 확인

    (ID! = 1) ..... 이전 ID 0 제로가되므로 전류 (Id)가 1 일 때 무시.

  16. ==============================

    16.이 문제는 하나 개의 쿼리로 해결 될 수있다

    이 문제는 하나 개의 쿼리로 해결 될 수있다

    select lft.id + 1 as missing_ids
from tbl as lft left outer join tbl as rght on lft.id + 1 = rght.id
where rght.id is null and lft.id between 1 and (Select max(id)-1 from tbl)

    MySQL을 테스트

  17. ==============================

    17.이것은 내가 TABLENAME으로 명명 한 테이블의 누락 된 ID를 찾는 데 사용 무엇

    이것은 내가 TABLENAME으로 명명 한 테이블의 누락 된 ID를 찾는 데 사용 무엇

    TABLENAME A에서 a.id + 1 missing_ID을 선택 여기서 a.id + 1 NOT IN (TABLENAME B로부터 선택 자료 여기서 b.id a.id = + 1) 및 a.id! = (ID 내림차순 제한하여 1 TABLENAME C의 순서에서 선택 ID)

    그것은 누락 된 ID를 반환합니다. 둘 (2) 이상의 연속없는 식별자가있는 경우 첫 번째 반환합니다.

    18. from https://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL CASE 표현식 구문?  (0) 2020.03.27
[SQL] 마이크로 소프트 SQL 서버 2000 에뮬레이션 MySQL의 LIMIT 절  (0) 2020.03.27
[SQL] 두 데이터베이스 사이에 외래 키 관계를 추가  (0) 2020.03.27
[SQL] MySQL은 - 이전 행, 그룹 별에서 값을 빼면  (0) 2020.03.27
[SQL] ID 열 값이 갑자기 SQL 서버 1001 점프 [중복]  (0) 2020.03.27
,

티스토리툴바