我试图在搜索中将 3 个 IN 语句与 ANd 结合起来。这是和示例,它不能完全工作,因为我需要它
SELECT name,thumbnail
FROM table1 WHERE
id IN (SELECT imgid FROM categories WHERE category = 22 )
AND id IN (SELECT imgid FROM namesearch WHERE name LIKE "%car%" )
OR id IN (SELECT imgid FROM tags WHERE tag LIKE "%jet%" )
从逻辑上讲,上面的例子在这样的组中,如果我没有错的话
(categorie=22 AND tags=car) OR (namesearch=jet)
如果我搜索在tag
找到的jet
,它不会过滤category=22
如何在这样的逻辑中对语句进行分组
(categorie=22) AND (tags=car OR|AND namesearch=jet)
如果我像这样放一个 AND,它会过滤类别
SELECT name,thumbnail
FROM table1 WHERE
id IN (SELECT imgid FROM categories WHERE category = 22 )
AND id IN (SELECT imgid FROM namesearch WHERE name LIKE "%car%" )
AND id IN (SELECT imgid FROM tags WHERE tag LIKE "%jet%" )
但是它会在名称搜索或标签中搜索还是同时搜索两者?
因为我不想在找到名称搜索时错过标签搜索,所以我需要它们,但名称搜索处于高优先级。
这是你想要的吗?
SELECT name, thumbnail
FROM table1
WHERE id IN (SELECT imgid FROM categories WHERE category = 22 ) AND
(id IN (SELECT imgid FROM namesearch WHERE name LIKE '%car%' ) OR
id IN (SELECT imgid FROM tags WHERE tag LIKE '%jet%' )
)
请注意,我对字符串常量使用了单引号。 这是很好的做法。