数据库求交集怎么做？

在开发的过程中遇到一个问题，一个文件和标签的多对多关系表，求取Tag1，Tag2，Tag3下的文件有哪些？
这个过程需要求取同表内的求交集操作，本篇POST聊一聊怎么样可以更高效的完成同表内的连接操作。

场景

一个文件和标签的多对多关系表，求取Tag1，Tag2，Tag3下的文件有哪些？

例如，求取符合 TAG_1 和 TAG_2 下面的所有文件。通过观察可以发现是F1和F3符合要求。因为F3包含标签，TAG_1, TAG_2, TAG_3，F1包含TAG_1, TAG_2, TAG_3，F2包含TAG_2。因此F1，F3符合要求。

如何求交集？

求取交集的办法有很多，课本上讲过的求取交集的办法就是使用 intersect，然后求取交集。但是很多DBMS并不支持这种求取交集的办法，那该怎么办？下面细细分析一波。

直接使用intersect求取交集

按照直觉来看，求取TAG_1的文件：F1, F3。求取TAG_2的文件：F1, F2,F3。最后得到的结果求交集，得到文件ID结果F1, F3。

1
2
3
4
5
6
7

select file_id
from tag_label
where tag_id = 'TAG_1'
intersect
select file_id
from tag_label
where tag_id = 'TAG_1';

使用连接查询来代替intersect求交集

因为交集操作这个关键字并不是所有的DBMS都拥有的，所以，在MySQL可以使用连接查询的方式来达到求取交集的目的。

1. TAG_1和TAG_2连接操作

   1 2 3 4

   SELECT TAG_1.`file_id` T1_F, TAG_2.`file_id` T2_F FROM (SELECT * FROM `tag_file` WHERE `tag_id` = 'TAG_1') TAG_1 LEFT JOIN (SELECT * FROM `tag_file` WHERE `tag_id` = 'TAG_2') TAG_2 ON TAG_1.`file_id` = TAG_2.`file_id`;

   T1_F	T2_F
   F1	F1
   F2	F2
   F3	NULL

2. TAG_1和TAG_2连接查询并且添加过滤操作

   1 2 3 4 5

   SELECT TAG_1.`file_id` FROM (SELECT * FROM `tag_file` WHERE `tag_id` = 'TAG_1') TAG_1 LEFT JOIN (SELECT * FROM `tag_file` WHERE `tag_id` = 'TAG_2') TAG_2 ON TAG_1.`file_id` = TAG_2.`file_id` WHERE TAG_1.`file_id` IS NOT NULL AND TAG_2.`file_id` IS NOT NULL;

   file_id
   F1
   F2

使用连接查询是一件代价很大的查询操作，虽然可以这种方式可以查询，但是并不是最好的。第二点，如果有多个标签ID需要构建，那么就需要构造一个很长的SQL查询语句，这样是一件不优雅的查询操作。综上所述，就需要一种新的查询操作来解决这个问题。

使用分组来求取交集

因为这种求取交集的办法很多DBMS并不支持，所以这种方法并不可行，那么该怎么办啊？不如换一种思路，按照文件名（file_id）分组。然后根据标签的长度来判断文件是不是同时属于这几个标签，从而得到文件ID列表。多说无益，不如做几个实验。

1. 查看包含标签列表的文件有哪些？

   1 2 3

   SELECT * FROM tag_file WHERE `tag_id` IN ('TAG_1', 'TAG_2');

   id	tag_id	file_id
   1	TAG_1	F1
   2	TAG_2	F1
   4	TAG_1	F2
   5	TAG_2	F2
   7	TAG_1	F3

   通过分析这些结果，发现，文件id一列的结果是，符合TAG_1或者符合TAG_2的结果，得到了并集的结果。但是想要求交集，又该如何进行？
2. 按照文件ID分组，查看tag的长度

   1 2 3 4

   SELECT `file_id`, COUNT(`tag_id`) tag_count FROM tag_file WHERE `tag_id` IN ('TAG_1', 'TAG_2') GROUP BY `file_id`;

   file_id	tag_count
   F1	2
   F2	2
   F3	1

   根据 GROUP BY 的性质，可以知道，GROUP BY是按照查询到的结果集，进行分组，因此可以避免连接查询的性能损耗。再其次，可以发现一个规律，因为分组是按照搜索结果分组，所以 tag_count 的长度不可能超过，需要查询的标签列表的长度的。根据场景也就是，tag_count 的结果一定小于等于2，而且长度等于2的结果，一定就是符合所有标签的。因此可以通过这种方式来将交集求出。
3. 求出符合条件的文件列表

   1 2 3 4 5

   SELECT `file_id` FROM tag_file WHERE `tag_id` IN ('TAG_1', 'TAG_2') GROUP BY `file_id` HAVING COUNT(`tag_id`) = 2;

   file_id
   F1
   F2

   最终通过这种方式求出结果。

利用MyBatis构造一个动态SQL：

1
2
3
4
5
6
7
8
9
10
11

<select id="getFileIdListByTagIds" resultType="String" parameterType="java.lang.List">
    <bind name="tagListSize" value="tagList.size()" />
    SELECT `file_id`
    FROM tag_file
    WHERE `tag_id` IN
        <foreach collection="tagList" item="tagId" open="(" sparator="," close=")">
            #{tagId}
        </foreach>
    GROUP BY `file_id`
    HAVING COUNT(`tag_id`) = #{tagListSize};
</select>

方法评价

这种方式主要用于同表以内的交集查询，但是并不适用广泛的求交集操作。