mysql - 在 MySQL 查询中查找具有多个 "OR"的部分匹配

Question

我需要在 MySQL 查询中查找部分或完全匹配。我使用“OR”在多个表中执行查询，需要知道是否满足所有条件或仅满足其中一些条件。

我相信最简单的方法是创建一个名为“partial”的新列，该列可以是 0 或 1(如果我错了，请随时纠正我)。

这是我的查询:

SELECT 
  id, confirmed, name, addressLine1, addressLine2, addressCity, addressPostcode,
  addressLat, addressLon,
  (6371 * acos( cos( radians( '53.649779' ) ) 
              * cos( radians( addressLat ) )
              * cos( radians( addressLon ) - radians( '-1.6026266' ) )
              + sin( radians( '53.649779' ) ) * sin( radians( addressLat ) ) ) 
  ) distance 
FROM TABLE_NAME 
WHERE active = '1' 
  AND deleted = '0' 
  AND 
  (
    id < 0 
   OR 
    id IN 
    (
      SELECT establishmentID 
      FROM appEstablishmentDrinks 
      WHERE active = '1' 
        AND deleted = '0' 
        AND manufacturerDrinkID IN (101) 
      GROUP BY establishmentID 
      HAVING count(distinct manufacturerDrinkID) = 1
    ) 
   OR
    id IN 
    (
      SELECT establishmentID 
      FROM appEstablishmentFacilities 
      WHERE active = '1' 
      AND deleted = '0' 
      AND facilityID IN (37)
    )
  ) 
HAVING distance < '15'

Answer 1

在 MySQL 中 true = 1 和 false = 0。所以只需添加条件:(id < 0) + (id in (...)) + (id in (...))并检查您是否获得全部三场比赛或更少。

SELECT
  id, confirmed, name, addressLine1, addressLine2, addressCity, addressPostcode,
  addressLat, addressLon, distance,
  case when count_matches = 3 then 'full' else 'partial' end as matching
FROM
(
  SELECT 
    id, confirmed, name, addressLine1, addressLine2, addressCity, addressPostcode,
    addressLat, addressLon,
    (6371 * acos( cos( radians( 53.649779 ) ) 
                * cos( radians( addressLat ) )
                * cos( radians( addressLon ) - radians( -1.6026266 ) )
                + sin( radians( 53.649779 ) ) * sin( radians( addressLat ) ) ) 
    ) as distance,
    (id < 0 )
     + 
    (
      id IN 
      (
        SELECT establishmentID 
        FROM appEstablishmentDrinks 
        WHERE active = 1 
          AND deleted = 0 
          AND manufacturerDrinkID IN (101) 
        GROUP BY establishmentID 
        HAVING count(distinct manufacturerDrinkID) = 1
      ) 
    )
     +
    (
      id IN 
      (
        SELECT establishmentID 
        FROM appEstablishmentFacilities 
        WHERE active = 1 
        AND deleted = 0 
        AND facilityID IN (37)
      )
    ) as count_matches
  FROM TABLE_NAME 
  WHERE active = 1 
    AND deleted = 0
) x
WHERE distance < 15
AND count_matches > 0;