带左联接的SQL中复杂和嵌套的WHERE条件

Question

我正在尝试用多个过滤器获取帖子,我的数据库如下所示,

  SELECT p.id, p.title
        FROM posts p
        JOIN meta m ON p.id = m.object_id
        WHERE m.object_name='post'

        AND (m.meta_key, m.meta_value) IN (('location', 'new city'),('post_type', 'section'))
        GROUP BY p.id, p.title
        HAVING COUNT(DISTINCT m.id) = 2

我尝试了上面的SQL，它可以工作，但问题是，如果我嵌套了两个以上的或条件和不同数量的过滤器，我会得到错误的结果。

just for explaining what i want , not sql :

            SELECT p.id, p.title
            FROM posts p
            JOIN meta m ON p.id = m.object_id
            WHERE m.object_name='post'
            AND
                                 (
                                    #SET 1 = (
                                       (m.meta_key = 'location' and m.meta_value = 'new city')
                                       or (m.meta_key = 'price' and m.meta_value = 100)
                                       or (m.meta_key = 'color' and m.meta_value = red)
                                    )

                                      OR

                                    #SET 2 = (
                                       m.meta_key = 'post_type' and m.meta_value = 'product'
                                       OR m.meta_key = 'location' and m.meta_value =  'delhi'
                                    )

                                 )
                          AND p.post_class='post'
              GROUP BY p.id, p.title
              HAVING if #SET 1 matched { COUNT(DISTINCT m.id) = 3 } elseIf #SET 2 matched { 
              COUNT(DISTINCT m.id) = 2 }

Answer 1

您必须在having子句中分别检查每个条件。假设键不重复，您可以省去distinct并使用：

having sum( meta_key in ('location', 'price', 'color') ) = 3 or
       sum( meta_key in ('post_type', 'location') ) = 2

注意：您可以为此使用count(distinc)，但我认为没有必要。