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

Question

我正在使用此 sql 返回基于具有 3 个元值的内部联接的结果。它似乎只适用于 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;

Answer 1

我认为您打算将 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');