MYSQL获取同时关注了某两个(或者N个)用户的用户

使用redis的set类型数据的话会比较容易,但是业务场景就是在mysql里面,因此也需要思考解决方法

表结构:

CREATE TABLE `table_name` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(12) NOT NULL COMMENT '用户ID',
`fans_id` int(12) unsigned NOT NULL COMMENT '粉丝id',
PRIMARY KEY (`id`),
KEY `fans_id` (`fans_id`) USING BTREE,
KEY `userid` (`userid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=312141 DEFAULT CHARSET=utf8 ;

1、比较直接的办法是直接联表:

SELECT * FROM 
(SELECT * FROM `table_name` WHERE userid=1060) t1 
INNER JOIN
 (SELECT * FROM `table_name` WHERE userid=106088) t2
ON t1.fans_id=t2.fans_id

或者

SELECT * FROM `table_name` t1 INNER JOIN `table_name` t2 ON t1.fans_id=t2.fans_id WHERE t1.userid=1060 AND t2.userid=106088

2、然而,如果这里的N>2,那就意味着要联N-1次,这样显然不太合理。所以还想了一种办法:

SELECT * FROM 
(SELECT fans_id,COUNT(id) as user_num FROM `table_name` WHERE userid IN (1060,106088) GROUP BY fans_id) t1 
WHERE user_num=2

如果有N个,那么user_num=N,IN里面也是N个用户ID

这样做还有个好处:如果要获取只关注了其中1个或者2个用户的用户,那么只要修改user_num就可以了

posted @ 2018-06-21 17:11  小昌君  阅读(492)  评论(0编辑  收藏  举报