SQL从具有特定条件的重复项中查找单个值

Question

我意识到这可能很简单。只是迟到了需要解释一下。

我有桌子：

mysql> SELECT * FROM employee_expert;
+------+---------+
| enum | package |
+------+---------+
| E246 | Excel   |
| E246 | MySQL   |
| E246 | Python  |
| E246 | Word    |
| E403 | Java    |
| E403 | MySQL   |
| E892 | Excel   |
| E892 | PHP     |
| E892 | Python  |
+------+---------+
9 rows in set (0.00 sec)

我需要找到元组的enum，而不是Python的结果应该是E403，因为它是唯一没有使用Python的。

试过

mysql> SELECT enum FROM employee_expert WHERE package != "Python" GROUP BY enum;
+------+
| enum |
+------+
| E246 |
| E403 |
| E892 |
+------+
3 rows in set (0.00 sec)         

但是，很明显，它刚刚归还了所有的enum.

Answer 1

一个选项是not in操作符：

SELECT DISTINCT enum
FROM   employee_expert
WHERE  enum NOT IN (SELECT enum 
                    FROM   employee_expert
                    WHERE  package = 'Python')

Answer 2

NOT IN将排除具有特定排除条件的数据子集：

SELECT DISTINCT(enum)
FROM employee_expert
WHERE enum NOT IN
(SELECT enum FROM employee_expert WHERE package = 'Python');

Distinct将排除重复项。Sql Fiddle在这里

Answer 3

我认为NOT EXISTS比NOT IN更快

SQL Fiddle

MySQL 5.5.32模式设置

CREATE TABLE employee_expert
    (`enum` varchar(4), `package` varchar(6))
;

INSERT INTO employee_expert
    (`enum`, `package`)
VALUES
    ('E246', 'Excel'),
    ('E246', 'MySQL'),
    ('E246', 'Python'),
    ('E246', 'Word'),
    ('E403', 'Java'),
    ('E403', 'MySQL'),
    ('E892', 'Excel'),
    ('E892', 'PHP'),
    ('E892', 'Python')
;

查询1

SELECT DISTINCT enum 
FROM employee_expert ee1
WHERE NOT EXISTS 
(SELECT 1 
 FROM employee_expert ee2 
 WHERE ee1.enum = ee2.enum AND ee2.package = 'Python')

结果

| ENUM |
|------|
| E403 |