查询所有和我看过相同电影的人 [英] Query on all the people who has watched the same movies as I did

问题描述

假设我看过不止一部电影，我如何找出所有看过我看过的所有电影的人?

Let say I have watched more than one movie, how do I find out all the people who also watched all the movies I watched?

     MovID  Name   Movie name
    ---------------------
    1     | me     |Batman
    2     | me     |Spider Man
    1     | Jason  |Batman
    2     | Jason  |Spider Man
    3     | Jason  |King kong 
    3     | Jim    |King kong
    4     | Jim    |Titanic

结果应该给我杰森.谢谢！

Which the result should give me Jason. Thank you!

推荐答案

这是一种方式:

select name
  from tbl
 where movid in (select movid from tbl where name = 'me')
   and name <> 'me'
 group by name
having count(*) = (select count(*) from tbl where name = 'me')

小提琴: http://sqlfiddle.com/#!9/3120e/2/0

这篇关于查询所有和我看过相同电影的人的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！