互粉的sql查询

建立表：

CREATE TABLE `tb_sns_attention` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`to_uid` int(11) NOT NULL DEFAULT '0',
`from_uid` int(11) NOT NULL DEFAULT '0',
`in_time` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `touid` (`to_uid`),
KEY `fromuid` (`from_uid`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

查询原理：利用自己本身的表做关联，关注人 = 被关注人

查询sql：

SELECT 
a.id,
FROM_UNIXTIME(a.in_time) as time,
a.from_uid,
a.to_uid,
b.from_uid,
b.to_uid
FROM tb_sns_attention as a JOIN tb_sns_attention as b ON a.from_uid=b.to_uid
WHERE a.to_uid=b.from_uid AND a.from_uid = 70
ORDER BY a.in_time DESC 
limit 0,10

效果如下：

转载于:https://www.cnblogs.com/hanybblog/p/7808060.html