除了多个 OR 之外，还需要 WHERE AND 子句 [英] required WHERE AND clause in addition to multiple OR's

问题描述

我需要返回 't'(线程)中包含 'menu_item_id' = 1 的所有项目.

I need to return all items from 't' (thread) that include 'menu_item_id' = 1.

另外，我需要根据f.tag过滤以上所有结果.

In addition, I need to filter all the above results based on f.tag.

SELECT t.*, u.username AS owner, uu.username AS replyname, a.location
FROM thread t
INNER JOIN filter_thread as ft ON t.thread_id = ft.thread_id
INNER JOIN filter as f ON f.filter_id = ft.filter_id
INNER JOIN users u ON t.owner_id = u.id 
INNER JOIN users uu ON t.last_post_id = uu.id
INNER JOIN user_profiles AS up ON u.id = up.user_id
INNER JOIN avatars AS a ON up.avatar_id = a.id
WHERE t.menu_item_id='1'
AND f.tag LIKE '%hello%'
OR f.tag LIKE '%world%'

问题区域:AND f.tag LIKE '%hello%'或 f.tag LIKE '%world%'

要意识到的重要一点是hello"和world"可能并不总是出现在查询中.此外，可能还会添加 3 个搜索，但是，将始终搜索 menu_item_id.

The important thing to realize is that 'hello' and 'world' might not always be in the query. In addition, there might be 3 more searches tacked onto it, however, the menu_item_id will always be searched for.

我不希望所有结果都需要每个 f.tags，我只想结果包含其中任何一个 (OR).

I don't want all the results to require each of the f.tags, i just want to the results to contain any of them (OR).

任何帮助将不胜感激.

推荐答案

使用括号指定AND和OR的优先级.

Use parenthesis to specify the precedence of the AND and OR.

a AND (b OR c)

对比

(a AND b) OR c

例如:

WHERE t.menu_item_id='1'
  AND (    f.tag LIKE '%hello%'
        OR f.tag LIKE '%world%'
      )

这将只返回 menu_item_id = '1' 且标签包含 'hello' 或 'world' 的行.

This will return only rows that have menu_item_id = '1' AND which have tag containing either 'hello' or 'world'.

这篇关于除了多个 OR 之外，还需要 WHERE AND 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！