mysql - 当前 MySQL IN 语句字符串 Cats 作为 "OR"- 需要它作为 "And"语句

Question

我有以下复杂的 SQL 语句，它使用“IN”来搜索多个类别。

SELECT MATCH(placename,fullloc) AGAINST ('' IN BOOLEAN MODE) as score, places.PlaceName, places.fullloc, places.PlaceID, places.ImageThumb, places.contributorid, places.verified, places.verified_by, places.verified_date, places.visibility 
FROM places 
INNER JOIN places_cats ON places.PlaceID = places_cats.PlaceID 
WHERE STATUS IN (1,0) 
  AND places_cats.CATID in (129,75,104) 
  AND (visibility = 1 OR (visibility in (2,3) 
  AND places.contributorid = '999999999'))
GROUP BY PlaceID 
ORDER BY score DESC

我希望能够将其从基本上使其成为多个“OR”语句的“IN”切换为给我多个“AND”语句的东西。是否有一个简单的 SQL 命令可以让我执行此操作，或者我是否必须实际创建多个“AND”语句？

CATID 列表可能会变得相当长。事实上，有时猫列表有 35 项。

Answer 1

IN 将一行中的一个值与列表进行比较。您要求的是完全不同:查找列表中的每个值是否存在单独的行。你不能用一个简单的运算符来做到这一点。 (如果您确实有 AND，则不会返回任何行，因为相同的值不能同时等于 129 和 75。)

您必须为您想要的每个类别加入一次或子查询一次。

SELECT ...
FROM places
INNER JOIN places_cats pc1 ON places.PlaceID = pc1.PlaceID AND pc1.CATID = 129
INNER JOIN places_cats pc2 ON places.PlaceID = pc2.PlaceID AND pc2.CATID = 75
INNER JOIN places_cats pc3 ON places.PlaceID = pc3.PlaceID AND pc3.CATID = 104

或者:

SELECT ...
FROM places
WHERE places.PlaceID IN (SELECT PlaceID FROM places_cats WHERE CATID = 129)
AND places.PlaceID IN (SELECT PlaceID FROM places_cats WHERE CATID = 75)
AND places.PlaceID IN (SELECT PlaceID FROM places_cats WHERE CATID = 104)

或者:

SELECT ...
FROM places
WHERE EXISTS (SELECT * FROM places_cats WHERE places_cats.PlaceID = places.PlaceID AND CATID = 129)
AND EXISTS (SELECT * FROM places_cats WHERE places_cats.PlaceID = places.PlaceID AND CATID = 75)
AND EXISTS (SELECT * FROM places_cats WHERE places_cats.PlaceID = places.PlaceID AND CATID = 194)

哪一个更容易生成(并且运行效率最高)取决于您的代码和 MySQL 的特性。