행에 발생하지 않는 값 목록을 반환하는 SELECT

행에 발생하지 않는 값 목록을 반환하는 SELECT

쿼리:

select id from users where id in (1,2,3,4,5)

사용자 테이블에 ID 1, 2, 3이 포함되어 있으면 1, 2, 3이 반환됩니다.4번과 5번을 돌려주는 질문을 원합니다.즉, 쿼리가 테이블에 존재하는 행을 반환하지 않고, 숫자 목록을 제공하고 테이블에 나타나지 않는 해당 목록의 값을 가져옵니다.

(해당되지 않는 몇 가지 답변에 이어 질문을 명확히 하기로 결정)

임시 테이블을 (명시적으로) 사용하지 않으려면 다음 작업을 수행합니다.

SELECT id FROM (
  (SELECT 1 AS id) UNION ALL
  (SELECT 2 AS id) UNION ALL
  (SELECT 3 AS id) UNION ALL
  (SELECT 4 AS id) UNION ALL
  (SELECT 5 AS id)
) AS list
LEFT JOIN users USING (id)
WHERE users.id IS NULL

하지만 상당히 추하고, 꽤 길며, 아이디 목록이 길다면 어떤 성능을 발휘할지 의문입니다.

동일한 필요성을 가지고 세션 중인 임시 테이블을 사용하여 BugFinder에 의해 답변을 구축했습니다.이렇게 하면 질의가 끝나면 자동으로 파기되기 때문에 이런 질의를 자주 실행할 것이기 때문에 집 청소에 대처할 필요가 없습니다.

임시 테이블을 만듭니다.

CREATE TEMPORARY TABLE tmp_table (id INT UNSIGNED);

tmp_table에 검사할 값을 채웁니다.

INSERT INTO tmp_table (id) values (1),(2),(3),(4),(5);

테이블을 생성하고 채운 상태에서 일반 테이블과 마찬가지로 쿼리를 실행합니다.

SELECT tmp_table.id
  FROM tmp_table
  LEFT JOIN users u
  ON tmp_table.id = u.id
  WHERE u.id IS NULL;

MySQL Temporary Tables에 대한 이 정보도 유용했습니다.

숫자가 고정된 목록인 것을 감안하면 말입니다.내가 생각하는 가장 빠른 방법은 테스트 테이블을 가지고 그 숫자들로 채워지는 것입니다.

테스트되지 않은 select 문 - 그러나 당신은 원칙을 따를 것입니다.

select test.number 
from test 
left join 
    users 
on 
    test.number = users.id 
where test.number <> users.id

그러면 일치하는 user.id 이 없어서 구멍을 메울 수 있는 모든 번호를 돌려받게 됩니다.

다른 옵션은 가능한 모든 ID를 포함하는 다른 테이블을 사용한 다음 여기에서 선택하는 것입니다.

mysql> describe ids;
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| id    | int(5) unsigned | NO   |     | 0       |       |
+-------+-----------------+------+-----+---------+-------+
1 row in set (0.05 sec)

mysql> select * from ids;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

mysql> select * from users;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)


mysql> select id from ids where id not in (select id from users);
+----+
| id |
+----+
|  4 |
|  5 |
+----+
2 rows in set (0.04 sec)

부작용 추가 - ids 테이블에 삽입하여 결과 목록을 확장할 수 있습니다.

select missing.id
  from
(select ELT(@indx, 1,2,3,4,5) as id, @indx:=@indx+1
  from (select @indx:=1) init,
    users
  where ELT(@indx, 1,2,3,4,5) is not null
) missing
  left join users u using(id)
where u.id is null;

여기 있는 건 다음과 같습니다.

• ELT변수와 함께@indx목록을 열로 '변환'할 수 있습니다.
• (select @indx:=1)indx를 1로 초기화하려면 필요합니다.
• 그리고.usersMySQL이 반복할 수 있도록 내부 선택의 테이블이 필요합니다. 따라서 목록의 크기가 사용자 테이블의 행 수를 초과할 수 없습니다. 내부 사용자 대신 충분히 큰 다른 테이블을 사용할 수 있는 경우 테이블 자체는 반복할 수 있는 것만 중요하지 않습니다.따라서 크기만 중요합니다.)
• ELT(@indx, 1,2,3,4,5) is not null중첩 선택의 조건은 목록 크기를 초과하는 인덱스에 도달하면 반복을 중지하는 것입니다.

- .left join를 합니다.null.

죄송합니다. 댓글을 추가할 수 없습니다. @Austin,

제 +1입니다.

어쨌든, 이것이 mysql에서 작동하는지 확실하지 않지만, atomic이 값 집합에 대해 연결된 결합을 선택하는 모든 것을 변경하면 다음과 같은 것이 있습니다.

SELECT id FROM (
    VALUES (1), (2), (3), (4), (5)
) AS list(id)
LEFT JOIN users USING (id)
WHERE users.id IS NULL

언급URL : https://stackoverflow.com/questions/10013475/select-that-returns-list-of-values-not-occurring-in-any-row