MySQL - Where IN 查询中的匹配和非匹配结果 [英] MySQL - matching and non-matching results in Where IN query

问题描述

我需要根据用户输入的关键字从数据库中查找匹配和不匹配的关键字.

I need to find matching and non matching keywords from the database against user entered keywords.

下面是我们存储所有已知关键字的数据库架构

Below is the database schema where we store all the known keywords

Table keywords
   keyword varchar

简单地查找匹配的关键字.我们使用以下查询来查找匹配的关键字

Finding matching keywords in simple. We use following query to find matching keywords

select keyword from keywords where keyword in ('abc', 'pqr', 'xyz')

其中abc"、pqr"、xyz"是用户提供的关键字.

Where 'abc', 'pqr', 'xyz' are keywords supplied by the user.

但是，我还需要找到数据库中不存在的关键字.绝对 NOT IN 不起作用，因为它会返回数据库中的所有标签，而不是不匹配的标签.例如，如果数据库中存在abc"和pqr"而xyz"不存在，我希望输出如下

However, I also need to find the keywords which are not present in the database. Definitely NOT IN does not work as it will return all the tags from the database rather than the one which was not matched. For example, if 'abc' and 'pqr' is present in the database and 'xyz' is not, I am expecting output as following

keyword present
abc       1
pqr       1
xyz       0

请帮帮我.

推荐答案

您可以尝试以下 - 使用 cte

You can try below - using cte

with cte1 as 
(
select 'abc' as key
union 
select 'pqr' union select 'xyz' 
)
select keyword,case when key is null then 0 else 1 end as is_present 
from keywords left join cte1 on keyword=key

这篇关于MySQL - Where IN 查询中的匹配和非匹配结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！