mysql查询 如何取交集

Question

表image_tags:

id   name
1    test1
2    test2

表image_taggables:

tag_id   post_id  
1            1
1            2
2            2

表image_posts:

id  content  title
1    c1        t1    
2    c2        t2    

有个搜索功能传入数组tag_ids[]，返回post_id

1、如tag_ids[]=1 返回post_id 1,2
2、如tag_ids[]=1,2 返回post_id 2
如何查询出2这种情况,1这种用in ()可以查询

Answer 1

mysql 没有支持这个的，我觉得可以先查出来，代码里做下逻辑吧

你可以还是用in查询

比如说 in (1,2)

然后查出来还是

1 1

1 2

2 2

可以转换成 post_id,然后后面是它对应的tagid列表。
你只要判断哪个post_id 对应的tagid 列表同时包含了你传入的条件就可以了。

复杂一点的搜索一般会直接用es之类的了

Answer 2

你这个问题要想使用mysql直接求解不太容易，首先你的tag_ids[]=1,2是不是会经常变动，有时候会有很多个？
要想取出交集你得分解你的数组，分成多个单独值以后分别取出post_id最后再把这些结果集inner join求交集才是你要的结果，逻辑太过复杂，使用mysql解决得不偿失，劝你代码层处理吧

Answer 3

sql是可以实现的吧。看看如下sql是否可以。

select post_id from image_taggables a where a.tag_id  in(1,2) group by a.post_id having (count(a.tag_id) >= 2);

其中第一组参数tag_ids[]，比如2中的数组值是1,2,第二种参数是你的tag_ids[]的长度，你2中的数组长度是2.

介于下面的评论,做下说明

select post_id from image_taggables a where a.tag_id  in(#{a}) group by a.post_id having (count(a.tag_id) >= #{b});

#{a}:tag_ids,比如tag_ids = [1,2,3,4],那么#{a}就是1,2,3,4
#{b}:tag_ids数组的length,比如tag_ids = [1,2,3,4],那么#{b} = tag_ids.lenght = 4;

image_taggables 表中数据应该不会重复，同一篇文章有二个相同的tag_id,不合理呀。这个可以从数据库层面控制（加唯一索引），或者代码层控制。

关于一次查询tag_ids过长问题。
可以分批处理呀
举个例子：
比如数组[1,2,3,4,5,6]，我们一次查询2个tag_id（实际开发中一次查询数量可以调大一些）。
第一次查询1,2,查出来的post_id 为[1,3]，
第二次查询3,4，查出来的post_id为[3,4],则只有3是可能的值。
以此类推下去。

Answer 4

@helloworld_ 写的sql，如果image_taggables表中没有重复值，逻辑上没什么问题。

实际写的时候在应用代码中，生成sql语句in后面的内容、count函数后的个数；如果需要post的详细信息，再关联post的表就可以了。

另外注意限制一下最大的tag数量，如果有几千个会造成生成的sql语句过大，有的数据库会有这方面的限制。