MySQL:选择不在列表中的项目 [英] MySQL: select items not in a list

问题描述

如何选择列表中而不是表格中的项目?例如，如果给我一个像:'apple', 'banana', 'carrot'的列表，而我有一个像这样的表:

How can I select items that are in a list but not in the table? For example, if I am given a list like: 'apple', 'banana', 'carrot' and I have a table like:

fruit:
------
apple
banana

我希望查询的结果为'carrot'，因为它是表中没有的列表中提供的元素.我该怎么做?

I want the result of my query to come as 'carrot', since it was the element in the list provided that is not present in the table. How do I do this?

推荐答案

实现此目的的最佳方法是将列表也放在表中:

The best way to accomplish this is to put your list in a table as well:

Table fruitable:
fruit 
------
apple
banana

Table fruitable_list:
fruit
------
apple
banana
carrot

然后您的查询将变为:

SELECT fruitable_list.fruit FROM fruitable_list
LEFT JOIN fruitable
  ON fruitable.fruit = fruitable_list.fruit
WHERE fruitable.fruit IS NULL

结果:

fruit
------
carrot

很难返回不在数据集中的结果集中的记录.

It's hard to return a record in a result set that's not in the data set.

这篇关于MySQL:选择不在列表中的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！