来自隔壁贴FB的SQL面经
听歌问题
table1: time|userid|songid|
talbe2: userid1|userid2
SQL第二问是要求找出含有pairs of friends的list, 里面的每对朋友都有两首以上共同听过的歌。我试着做了一下,我希望其实是user1, user2的两个column的结果,我这里用了concat, 不是很理想,但想不出来更好的。抛砖引玉,请大家多多post更好的解法。
select
concat(userid1, userid2) as friend_pairs
count() as song_ct
from
(select
distinct
userid1, userid2, b.songid as song1, c.songid as song2
from table2 a
left outer join table1 b on a.userid1 = b.userid
left outer join table1 c on a.userid2 = c.userid) a
where a.song1 = a.song2 and a.song1 is not null and a.song2 is not null
group by concat(userid1, userid2)
having count() >= 2