有没有办法只检索 IN（或 NOT IN）子句中与表中的记录不匹配的值？

发布于 2024-12-13 06:11:39 字数 193 浏览 1 评论 0原文

这可能是一个奇怪的问题。目前，我有一个包含大量数据的表。我使用 PHP 动态创建 IN 子句。

SELECT * FROM table
WHERE field IN ('str1', 'str2', [...])

假设没有与“str1”或“str2”匹配的记录，有没有办法检索“str1”和“str2”？

原文

This might be a weird question. Currently, I have a table with a bunch of data in it. Using PHP, I am dynamically creating the IN clause.

SELECT * FROM table
WHERE field IN ('str1', 'str2', [...])

Let's say no records matched with 'str1' or 'str2', is there a way to retrieve 'str1' and 'str2'?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

北风几吹夏 2024-12-20 06:11:39

SELECT dmy.id
FROM (
   SELECT 'str1' as id
   UNION ALL
   SELECT 'str2' 
   UNION ALL
   .....
) dmy
LEFT JOIN some_table ON some_table.field = dmy.id
WHERE some_table.field IS NULL

根据特殊要求，这里有一个解释：

派生表（内部选择）只是创建一个包含所有所需值的“虚拟”表。然后将该虚拟表外部连接到真实表。外部连接表 (some_table) 中没有值 (NULL) 的任何内容都不存在于虚拟“表”的值列表中。

要查看派生表的效果，只需单独运行它，无需连接：

SELECT dmy.id
FROM (
   SELECT 'str1' as id
   UNION ALL
   SELECT 'str2' 
   UNION ALL
   .....
) dmy

SELECT dmy.id
FROM (
   SELECT 'str1' as id
   UNION ALL
   SELECT 'str2' 
   UNION ALL
   .....
) dmy
LEFT JOIN some_table ON some_table.field = dmy.id
WHERE some_table.field IS NULL

On special request, here is an explanation:

The derived table (the inner select) just creates a "dummy" table that contains all the desired values. Then this dummy table is outer joined to the real table. Anything that has no value (NULL) in the outer joined table (some_table) does not exist in the list of values from the dummy "table".

To see the effect of the derived table just run it stand alone without the join:

SELECT dmy.id
FROM (
   SELECT 'str1' as id
   UNION ALL
   SELECT 'str2' 
   UNION ALL
   .....
) dmy

回复收藏 0 原文

提笔落墨 2024-12-20 06:11:39

当您动态构建查询时，请在数组中保留 str 值的记录。然后，当您运行查询并检查结果数时，如果没有结果，您可以对字符串数组执行任何需要执行的操作（我猜您想将其记录在某处）。

回复收藏 0 原文

临风闻羌笛 2024-12-20 06:11:39

或者简单地

SELECT field from table WHERE field IN ( .. ) GROUP BY field;

返回一个 1 数组，该数组包含至少有一个匹配的值列表。
然后通过将初始列表与获得的数组进行比较来找出不匹配的值，就很容易找出来了

Or simply

SELECT field from table WHERE field IN ( .. ) GROUP BY field;

this wil return a 1 an array containing on the list of values that have at least one match.
Then it's easy to figure out by comparing the initial list with the obtained array to figure out values with no match

回复收藏 0 原文

~没有更多了~