MySQL NOT IN来自同一表中的另一列 [英] MySQL NOT IN from another column in the same table

问题描述

我想运行mysql查询以从表films中选择所有行，其中title列的值在另一列(collection)的所有值中都不存在.

I want to run a mysql query to select all rows from a table films where the value of the title column does not exist anywhere in all the values of another column (collection).

这是我的桌子的简化版本，其中包含以下内容:

Here is a simplified version of my table with content:

mysql> select * from films;
+----+--------------+--------------+
| id | title        | collection   |
+----+--------------+--------------+
|  1 | Collection 1 | NULL         |
|  2 | Film 1       | NULL         |
|  3 | Film 2       | Collection 1 |
+----+--------------+--------------+

这是我的查询:

mysql> SELECT * FROM films WHERE title NOT IN (SELECT collection FROM films);
Empty set (0.00 sec)

在此示例中，我想选择标题为Film 1和Film 2的行，但我的查询未返回任何行.

In this example, I would want to select the rows with titles Film 1 and Film 2, but my query is returning no rows.

这是表结构:

CREATE TABLE `films` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL DEFAULT '',
  `collection` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

推荐答案

SELECT * 
FROM films 
WHERE title NOT IN (SELECT collection FROM films where collection is not null);

SQLFiddle: http://sqlfiddle.com/#!2/76278/1

SQLFiddle: http://sqlfiddle.com/#!2/76278/1

这篇关于MySQL NOT IN来自同一表中的另一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！