除非值被另一行取消，否则选择行

Question

FROM    TO     TYPE   
----   ----    ----
John   Jane    Like
Jill   Jane    Like
Jane   John    Hide

如何编写如下SELECT语句：

SELECT * FROM table WHERE to='Jane' UNLESS from='Jane' AND type='hide'

这样，在上表中，它将返回John和Jill，减去John，因为Jane选择隐藏John，这意味着只会返回Jill。

Answer 1

使用not exists

select t.*
from t
where t.to = 'Jane' and
      not exists (select 1
                  from t t2
                  where t2.from = t.to and t2.type = 'Hide'
                 );