Navicat SQL - 找出互赞的用户

题目

id1是用户id，id2是被点赞用户id

#建立活跃表
create table LikeTable( 
id1 varchar(20) not null, 
id2 varchar(20))
ENGINE=InnoDB DEFAULT CHARSET=utf8;

#插入活跃数据
INSERT INTO LikeTable 
(id1, id2) 
VALUES 
('a','b'),
('a', 'c'),
('a' ,'d'),
('b', 'a'),
('b', 'c'),
('b', 'd'),
('b', 'e'),
('c', 'a'),
('c', 'f'),
('c', 'b'),
('e', 'a'),
('e', 'b'),
('a', 'f'); 

select t1.id1 as id1,t1.id2 as id2,t2.id2 as id3
from
(select id1,id2 from LikeTable) t1
inner join 
(select id1,id2 from LikeTable) t2
on t1.id2 = t2.id1
where t1.id1 = t2.id2

b a b
c a c
a b a
c b c
e b e
a c a
b c b
b e b

-- 互相点过赞的用户
select t1.id1 as id1,t1.id2 as id2
from
(select id1,id2 from LikeTable) t1
inner join 
(select id1,id2 from LikeTable) t2
on t1.id2 = t2.id1
where t1.id1 = t2.id2

b a
c a
a b
c b
e b
a c
b c
b e

-- 参与过互赞的去重用户
select distinct t1.id1 as id1
from
(select id1,id2 from LikeTable) t1
inner join 
(select id1,id2 from LikeTable) t2
on t1.id2 = t2.id1
where t1.id1 = t2.id2

优化

会出现ab互赞,ba互赞这种业务逻辑上其实是重复的情况

select t1.id1 as id1,t1.id2 as id2,t2.id2 as id3,t2.id1 as id4
from
(select id1,id2 from LikeTable) t1
inner join 
(select id1,id2 from LikeTable) t2
on t1.id2 = t2.id1
where t1.id1 = t2.id2
and t1.id2 not in t2.id1

错误答案

select distinct * from
(select id1 from LikeTable) t1
inner join 
(select id2 from LikeTable) t2
on t1.id1 = t2.id2

b b
c c
a a
e e

Tips1

点赞数据库设计 ： https://bbs.csdn.net/topics/391001223

Tips2

快手面经