Hugh's Blog

MySQL 交集、并集与差集

 

最近碰到一个简单的需求:获取多个分类下的文章,中间有个关联表;有点类似于根据分类表与文章表获取交集 (关联表) 的情况,过程也简单,作下记录。

使用以下的数据来测试:

mysql> SELECT * FROM `table_a`;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+

mysql> SELECT * FROM `table_b`;
+----+------+
| id | name |
+----+------+
|  1 | c    |
|  2 | d    |
|  3 | e    |
|  4 | f    |
|  5 | g    |
+----+------+

交集

使用子查询或者 LEFT JOIN 都行。

mysql> SELECT * FROM `table_a` WHERE `name` IN (SELECT `name` FROM `table_b`);
+----+------+
| id | name |
+----+------+
|  3 | c    |
|  4 | d    |
+----+------+

mysql> SELECT a.* FROM `table_a` AS a LEFT JOIN `table_b` AS b USING(name) WHERE b.id IS NOT NULL;
mysql> SELECT a.* FROM `table_a` AS a LEFT JOIN `table_b` AS b ON a.name = b.name WHERE b.id IS NOT NULL;
+----+------+
| id | name |
+----+------+
|  3 | c    |
|  4 | d    |
+----+------+

并集

使用 UNION (ALL) 语句,注意结果列必须相同。

mysql> SELECT * FROM `table_a` UNION SELECT * FROM `table_b`;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  1 | c    |
|  2 | d    |
|  3 | e    |
|  4 | f    |
|  5 | g    |
+----+------+

# 指定 `name` 会对结果去重,除非使用 UNION ALL
mysql> SELECT `name` FROM `table_a` UNION SELECT `name` FROM `table_b`;
+------+
| name |
+------+
| a    |
| b    |
| c    |
| d    |
| e    |
| f    |
| g    |
+------+

差集

使用子查询或者 LEFT JOIN 都行。

mysql> SELECT * FROM `table_a` WHERE `name` NOT IN (SELECT `name` FROM `table_b`);
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+

mysql> SELECT a.* FROM `table_a` AS a LEFT JOIN `table_b` AS b USING(name) WHERE b.id IS NULL;
mysql> SELECT a.* FROM `table_a` AS a LEFT JOIN `table_b` AS b ON a.name = b.name WHERE b.id IS NULL;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+

其实都可以用 LEFT JOIN、INNER JOIN 来获取结果集,然后根据 NULL 来判断就行了。

回到最开始的需求:

mysql> SELECT * FROM `category`;
+----+-------+
| id | title |
+----+-------+
|  1 | cat#1 |
|  2 | cat#2 |
|  3 | cat#3 |
|  4 | cat#4 |
+----+-------+

mysql> SELECT * FROM `post`;
+----+--------+
| id | title  |
+----+--------+
|  1 | post#1 |
|  2 | post#2 |
|  3 | post#3 |
|  4 | post#4 |
+----+--------+

mysql> SELECT * FROM `relationship`;
+-----+-----+
| cid | pid |
+-----+-----+
|   2 |   3 |
|   3 |   1 |
|   4 |   2 |
|   3 |   3 |
|   2 |   4 |
+-----+-----+

# 取分类 23 下的文章
mysql> SELECT p.* FROM `post` AS p LEFT JOIN `relationship` AS r ON p.id = r.pid WHERE r.cid = 2 AND p.id IN (SELECT pid FROM `relationship` WHERE `cid` = 3) GROUP BY p.id;
+----+--------+
| id | title  |
+----+--------+
|  3 | post#3 |
+----+--------+

看起来还有个小问题,如果很多分类,比如 2、3、4、5 等,那子查询会有点多,语句拼起来也麻烦。