行是否存在且存在多个位置 [英] Does row exist and multiple where

问题描述

我刚刚问了这个问题Multiple Where conditions，但意识到还有更多(和不想混淆另一个问题).

I just asked this question Multiple Where conditions, but realised there was more to it (and didn't want to confuse the other question).

我有一张看起来像这样的表格:

I have a table that looks like this:

meta_id - id - meta_key     - meta_value
1         1    school         Some School 1
2         2    school         Some School 2
3         2    hidden         1
4         3    school         Some School 3
5         4    school         Some School 4
6         5    school         Some School 5
7         5    hidden         1

感谢我之前的问题，我有这个语法:

Thanks to my previous question I have this syntax:

SELECT DISTINCT m1.id 
FROM metadata m1
join metadata m2 on m1.id = m2.id
WHERE (m1.meta_key = 'school' AND m1.meta_value = 'Some School 1') 
AND (m2.meta_key = 'hidden' AND m2.meta_value = '1')

如果 school = Some School 1 和 hidden = 1

但问题是行 hidden 仅在隐藏时才存在，如果不隐藏则不存在 - 所以它不像 hidden = 0 那样容易code> 表示不隐藏，hidden = 1 表示隐藏.

But the problem is that the row hidden is only there if it is hidden, it doesn't exist if its not hidden - so its not as easy as hidden = 0 means its not hidden and hidden = 1 means its hidden.

所以我需要找出 school = Some School 1 并且对于相同的 id，行 hidden 不存在.

So I need to find out school = Some School 1 and that for the same the id, the row hidden doesn't exist.

推荐答案

SELECT DISTINCT m1.id 
FROM metadata m1
WHERE 
    m1.meta_key = 'school' 
AND m1.meta_value = 'Some School 1' 
AND NOT EXISTS 
(
  SELECT * FROM metadata m2
  WHERE 
      m2.meta_key = 'hidden' 
  AND m2.meta_value = '1'
  AND m2.Id = m1.Id
)

这篇关于行是否存在且存在多个位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！