Navicat SQL-互いに称賛するユーザーを探し出します

文書ディレクトリ

題

最適化

誤答

Tips1

Tips2

タイトル
id 1はユーザid,id 2は称賛されたユーザ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
速手経