MySQL实现EXCEPT

1.问题描述

我有这样一张表
uid u_a u_b
11 a 0
11 b 1
12 a 1
12 b 0
13 b 1

我想查u_a=a时u_b=0且u_a=b时 u_b=1的u_id.上面的结果为11

2.我的回答

分析：
我们可以用EXCEPT实现此功能，但MySQL中没有EXCEPT，所以需要变通实现。

供您参考：

第一步，创建测试数据库和表，插入测试数据；

mysql> CREATE DATABASE robin DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
mysql> USE robin;
Database changed
mysql> CREATE TABLE test(uid int(4), u_a VARCHAR(2), u_b VARCHAR(2));
Query OK, 0 rows affected (0.15 sec)
mysql> INSERT INTO test VALUES(11,'a','0');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test VALUES(11,'b','1');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test VALUES(12,'a','1');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test VALUES(12,'b','0');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test VALUES(13,'b','1');
Query OK, 1 row affected (0.01 sec)

第二步，实现功能：

mysql>
SELECT DISTINCT ta.uid
FROM test tb
LEFT JOIN test ta ON(ta.u_a='a'
AND ta.u_b='0'
AND tb.u_a='b'
AND tb.u_b='1'
AND ta.uid=tb.uid)
WHERE ta.uid IS NOT NULL;
+------+
| uid |
+------+
| 11 |
+------+
1 row in set (0.00 sec)
Enjoy!
From My Zhihu Anwser.