mysql - 排除具有相关值的行

Question

假设我有一个如下所示的表格:

++++++++++++++++++++++++++++++++++++++++
  url        | fieldname    | value
++++++++++++++++++++++++++++++++++++++++
  a,b,c      | RATE         | 45
----------------------------------------
  a,b,c      | PLAY         | 5
----------------------------------------
  a,b,c      | DATE         | 2013-05-20
----------------------------------------
  d,e,f      | PLAY         | 2
----------------------------------------
  d,e,f      | DATE         | 2013-01-01
++++++++++++++++++++++++++++++++++++++++

如果具有相同 url 值的行之一具有特定 (已知!)fieldname 列中的值？

举个例子，如果我想排除 fieldname 中带有 RATE 的行，以及“相关”行(在url 列)——这样它就会返回这样的表？:

++++++++++++++++++++++++++++++++++++++++
  url        | fieldname    | value
++++++++++++++++++++++++++++++++++++++++
  d,e,f      | PLAY         | 2
----------------------------------------
  d,e,f      | DATE         | 2013-01-01
++++++++++++++++++++++++++++++++++++++++

再次请记住，url 值不必在查询中指定。

Answer 1

这是“集合内集合”查询的示例。我喜欢将聚合与 having 子句一起使用，因为它是最通用的方法。

以下查询获取您感兴趣的 URL:

select url
from t
group by url
having sum(case when fieldname = 'RATE' then 1 else 0 end) = 0;

要获取原始表中的所有行，您需要连接回它:

select t.*
from t join
     (select url
      from t
      group by url
      having sum(case when fieldname = 'RATE' then 1 else 0 end) = 0
     ) turl
     on t.url = turl.url