MySQL 테이블에서 누락된 ID 가져오기

MySQL 테이블에서 누락된 ID 가져오기

MySQL에 이 테이블이 있습니다. 예:

ID | Name
1  | Bob
4  | Adam
6  | Someguy

눈치채면 ID 번호(2, 3, 5)가 없습니다.

MySQL이 누락된 ID(이 경우 "2,3,5")에만 응답하도록 쿼리를 작성하려면 어떻게 해야 합니까?

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

이 링크가 http://www.codediesel.com/mysql/sequence-gaps-in-mysql/ 에도 도움이 되길 바랍니다.

보다 효율적인 쿼리:

SELECT (t1.id + 1) as gap_starts_at, 
       (SELECT MIN(t3.id) -1 FROM my_table t3 WHERE t3.id > t1.id) as gap_ends_at
FROM my_table t1
WHERE NOT EXISTS (SELECT t2.id FROM my_table t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL

여러 범위의 ID를 반환하는 대신 누락된 모든 ID를 각 행에서 검색하려는 경우 다음 작업을 수행할 수 있습니다.

SELECT id+1 FROM table WHERE id NOT IN (SELECT id-1 FROM table) ORDER BY 1

쿼리는 매우 효율적입니다.그러나 가장 높은 ID 번호에 1을 더한 것과 같은 추가 행이 끝에 하나 더 포함됩니다.반환된 행 수(mysqli_num_rows)를 확인한 후 다음을 사용하여 서버 스크립트에서 이 마지막 행을 무시할 수 있습니다.for행 수가 1보다 클 경우 루프합니다(쿼리는 항상 적어도 하나의 행을 반환합니다).

편집: 누락된 번호가 연속적인 경우(즉, 서로 바로 옆에 있는 경우) 원래 솔루션이 누락된 모든 ID 번호를 반환하지 않는다는 것을 최근에 발견했습니다.그러나 쿼리는 누락된 숫자가 있는지 여부를 매우 신속하게 파악하는 데 유용하며, Hagensoft의 쿼리(상위 응답)와 함께 사용할 경우 시간을 절약할 수 있습니다.즉, 이 쿼리를 먼저 실행하여 누락된 ID를 테스트할 수 있습니다.발견된 것이 있으면 즉시 hagensoft의 쿼리를 실행하여 누락된 정확한 ID를 식별할 수 있습니다(시간 절약은 아니지만 속도는 전혀 느리지 않습니다).아무것도 발견되지 않으면 hagensoft의 쿼리를 실행할 필요가 없기 때문에 상당한 시간이 절약될 수 있습니다.

Ivan의 답변에 조금 더 덧붙이자면, 이 버전은 1이 존재하지 않는 경우 처음에 누락된 숫자를 보여줍니다.

SELECT 1 as gap_starts_at,
       (SELECT MIN(t4.id) -1 FROM testtable t4 WHERE t4.id > 1) as gap_ends_at
FROM testtable t5
WHERE NOT EXISTS (SELECT t6.id FROM testtable t6 WHERE t6.id = 1)
HAVING gap_ends_at IS NOT NULL limit 1
UNION
SELECT (t1.id + 1) as gap_starts_at, 
       (SELECT MIN(t3.id) -1 FROM testtable t3 WHERE t3.id > t1.id) as gap_ends_at
FROM testtable t1
WHERE NOT EXISTS (SELECT t2.id FROM testtable t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL;

한 쿼리에서 간격을 시작하고 한 쿼리에서 간격을 종료하는 것이 훨씬 효율적입니다.

저는 1,800만 개의 레코드를 가지고 있었고 두 개의 결과를 얻는 데 각각 1초도 걸리지 않았습니다.제가 그것들을 정리하려고 시도했을 때, 제 질문은 한 시간 후에 타임아웃되었습니다.

격차의 시작:

SELECT (t1.id + 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS 
    (SELECT t2.id 
    FROM sequence t2 
    WHERE t2.id = t1.id + 1);

격차 해소:

SELECT (t1.id - 1) as MissingID
FROM sequence t1
WHERE NOT EXISTS 
    (SELECT t2.id 
    FROM sequence t2 
    WHERE t2.id = t1.id - 1);    

위의 쿼리는 두 개의 열을 제공하므로 한 열에서 누락된 숫자를 가져올 수 있습니다.

select start from 
(SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM sequence AS a, sequence AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)) b
UNION
select c.end from (SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM sequence AS a, sequence AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)) c order by start;

윈도우 기능(mysql 8에서 사용 가능)을 사용하여 시스템의 공백 찾기id열은 다음과 같이 표현할 수 있습니다.

WITH gaps AS
(
    SELECT
        LAG(id, 1, 0) OVER(ORDER BY id) AS gap_begin,
        id AS gap_end,
        id - LAG(id, 1, 0) OVER(ORDER BY id) AS gap
    FROM test
)
SELECT
    gap_begin,
    gap_end
FROM gaps
WHERE gap > 1
;

만약 당신이 이전 버전의 mysql에 있다면 당신은 변수에 의존해야 할 것입니다 (이른바 가난한 사람의 창 함수 관용구).

SELECT
   gap_begin,
   gap_end
FROM (
     SELECT
         @id_previous AS gap_begin,
         id AS gap_end,
         id - @id_previous AS gap,
         @id_previous := id
     FROM (
         SELECT
             t.id
         FROM test t
         ORDER BY t.id
     ) AS sorted
     JOIN (
         SELECT
             @id_previous := 0
     ) AS init_vars
 ) AS gaps
WHERE gap > 1
;

결측값을 확인하려는 ID의 범위를 알고 있는 경우 다음 작업을 간단히 수행할 수 있습니다.

SELECT ID
FROM Table
WHERE ID NOT IN (
    SELECT ID 
    FROM Table
    WHERE ID BETWEEN 1 AND 6
)

수백만 줄의 데이터를 검색할 수 있는 더 가벼운 방법을 원한다면,

SET @st=0,@diffSt=0,@diffEnd=0;
SELECT res.startID, res.endID, res.diff
  , CONCAT(
    "SELECT * FROM lost_consumer WHERE ID BETWEEN "
    ,res.startID+1, " AND ", res.endID-1) as `query`
FROM (
SELECT
  @diffSt:=(@st) `startID`
  , @diffEnd:=(a.ID) `endID`
  , @st:=a.ID `end`
  , @diffEnd-@diffSt-1 `diff`
  FROM consumer a 
ORDER BY a.ID
) res
WHERE res.diff>0;

이 http://sqlfiddle.com/ #!9/3ea00c/9를 확인하십시오.

언급URL : https://stackoverflow.com/questions/12325132/mysql-get-missing-ids-from-table