从where子句的给定集中选择表中不存在的ID [英] Select IDs that do not exist in a table from a given set in where clause

问题描述

我有一组用户ID: (512,5,13,14,67) 和包含以下内容的表:

I have a set of userID's: (512,5,13,14,67) and a table containing the following:

+----+--------+
| Id | userID |
+----+--------+
|  1 |    512 |
|  2 |     13 |
|  3 |     14 |
|  4 |     51 |
|  5 |      6 |
+----+--------+

该集中的某些用户ID在表中不存在.例如. userID 5和userID 64不存在.

Some of those userID's in the set do not exist in a table. E.g. userID 5 and userID 64 do not exist.

当我执行Select * from mytable where userID NOT IN (512,5,13,14,67)时，它将显示具有用户ID 6和51

When I execute Select * from mytable where userID NOT IN (512,5,13,14,67) it will display the rows with userIDs 6 and 51

+----+--------+
| Id | userID |
+----+--------+
|  4 |     51 |
|  5 |      6 |
+----+--------+

我想做类似的事情:
SELECT userID FROM my TABLE WHERE NOT EXISTS IN (1,5,10,15)

I would like to do something like:
SELECT userID FROM my TABLE WHERE NOT EXISTS IN (1,5,10,15)

并得到结果:

+-------+--------+
|  Id   | userID |
+-------+--------+
| NULL  |      5 |
| NULL  |     64 |
+-------+--------+

我的表包含一百万行，搜索集可能包含1000个要搜索的ID.

My table contain million rows and the search set may contain 1000 ids to search for.

推荐答案

如果我正确理解您的问题，那么您正在寻找.

If i understand your problem correctly you are looking for.

SELECT 
   NULL AS id 
 , search_filter.userID
FROM (
  SELECT 
   5 AS userID
  UNION
  SELECT
    64 AS userID
  # [...]
) AS search_filter
LEFT JOIN
 your_table
ON
 search_filter.userID = your_table.userID
WHERE 
 your_table.userID IS NULL

搜索集可能是1000个ID，因此我无法执行类似的查询 那个.

The search set could be 1000 ids so I cannot execute the query like that.

然后使用将由嵌套SUBSTRING_INDEX()函数解析的CSV搜索列表与SQL数字生成器结合使用是您唯一的也是最佳选择.

Then using a CSV search list which will be parsed by nested SUBSTRING_INDEX() functions in combination with a SQL number generator is your only and best option.

SELECT 
   NULL AS id 
 , search_filter.userID
FROM (
  SELECT 
   DISTINCT 
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(init_search.search_list, ',', sql_number_generator.number), ',', -1
    ) AS userID
FROM (

SELECT
  @number := @number + 1 AS number
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) row1
  CROSS JOIN
  (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) row2
  CROSS JOIN
  (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) row3 
  CROSS JOIN
  (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) row4    
  CROSS JOIN 
  (SELECT @number:=0) AS init_user_params
)  
) AS sql_number_generator
CROSS JOIN (
 SElECT '512,5,13,14,67' AS search_list 
) AS init_search
) AS search_filter
LEFT JOIN
 your_table
ON
 search_filter.userID = your_table.userID
WHERE 
 your_table.userID IS NULL

结果

| id  | userID |
| --- | ------ |
|     | 5      |
|     | 67     |

请参见演示

这篇关于从where子句的给定集中选择表中不存在的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！