使用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
MySQL多表查詢。或者
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就可以了