mysql - 无法查询内部联接上的多个值

我正在使用此sql返回基于具有3个meta值的内部联接的结果。它似乎只能与1 AND（）一起使用，当我将其他两个相加时，它将返回0个结果。

SELECT * FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE wp_posts.post_type = 'plot'
AND wp_posts.post_status = 'publish'
AND ( wp_postmeta.meta_key = 'plot_type' AND wp_postmeta.meta_value = 'Cottage' )
AND ( wp_postmeta.meta_key = 'number_of_bedrooms' AND wp_postmeta.meta_value = '2' )
AND ( wp_postmeta.meta_key = 'property' AND wp_postmeta.meta_value = '446' )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_title ASC;

最佳答案

我认为您打算将OR与其他2个一起使用（请参见下文）。同一字段不能是2个不同的事物，这就是为什么您得到0个结果的原因。

SELECT *
  FROM wp_posts
 INNER JOIN wp_postmeta
    ON (wp_posts.ID = wp_postmeta.post_id)
 WHERE wp_posts.post_type = 'plot'
   AND wp_posts.post_status = 'publish'
   AND ((wp_postmeta.meta_key = 'plot_type' AND
       wp_postmeta.meta_value = 'Cottage') OR
       (wp_postmeta.meta_key = 'number_of_bedrooms' AND
       wp_postmeta.meta_value = '2') OR (wp_postmeta.meta_key = 'property' AND
       wp_postmeta.meta_value = '446'))
 GROUP BY wp_posts.ID
 ORDER BY wp_posts.post_title ASC;

编辑，请尝试以下操作：

select * from wp_posts
join wp_postmeta on wp_posts.ID = wp_postmeta.post_id
where wp_posts.post_type = 'plot'
  and wp_posts.post_status = 'publish'
  and concat(wp_postmeta.meta_key,'|',wp_postmeta.meta_value)
        in ('plot_type|Cottage',
           'number_of_bedrooms|2',
           'property|446');