多个 Where 条件 [英] Multiple Where conditions

问题描述

无法正确使用此语法:

SELECT DISTINCT id 
FROM metadata 
WHERE (meta_key = 'school' AND meta_value = 'Some School') 
AND WHERE (meta_key = 'hidden' AND meta_value = '1')

它在第 4 行失败...

Its failing at line 4...

更新:表格如下所示:

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

更新:我有一个相关的，在这里扩展是否行存在且多个 where

UPDATED: I have a related, extended here Does row exist and multiple where

推荐答案

您不需要第二个 where，第二个 and 可能旨在成为 <代码>或:

You do not need a second where, and the second and was probably intended to be an or:

SELECT DISTINCT id 
FROM metadata 
WHERE (meta_key = 'school' AND meta_value = 'Some School') 
OR (meta_key = 'hidden' AND meta_value = '1')

(我认为你想要一个 or 的原因是因为否则你有一个矛盾的子句meta_key = 'school' AND meta_key = 'hidden'，这是总是假的).

(the reason I think you wanted an or is because otherwise you have a conjunction of contradictory clauses meta_key = 'school' AND meta_key = 'hidden', which is always false).

EDIT :为了回应 OP 关于他试图获得的结果的评论，这里有一个不同的查询:

EDIT : In response to OP's comment about the results he is trying to get, here is a different query:

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') 
AND (m2.meta_key = 'hidden' AND m2.meta_value = '1')

现在连词子句不再矛盾，因为它们指的是两个不同的行m1和m2.

Now the conjunction clauses are no longer contradictory, because they refer to two different rows m1 and m2.

这篇关于多个 Where 条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！