如何对WordPress的wp_postmeta表执行带双重WHERE条件的查询

方案1：GROUP BY + HAVING 聚合查询（写法简洁，适合少量筛选条件） #

SELECT post_id
FROM wp_postmeta
WHERE 
  (meta_key = 'leather' AND meta_value = 'calf')
  OR 
  (meta_key = 'gender' AND meta_value = 'woman')
GROUP BY post_id
-- 确保同一个post_id同时匹配两个不同的meta键值条件
HAVING COUNT(DISTINCT meta_key) = 2;

方案2：自连接查询（扩展性强，适合多条件叠加的场景） #

SELECT DISTINCT p1.post_id
FROM wp_postmeta p1
-- 关联同一张表匹配第二个条件
INNER JOIN wp_postmeta p2 
  ON p1.post_id = p2.post_id
WHERE 
  p1.meta_key = 'leather' AND p1.meta_value = 'calf'
  AND 
  p2.meta_key = 'gender' AND p2.meta_value = 'woman';

扩展：关联产品主表获取完整产品信息（适配WooCommerce场景） #

SELECT p.*
FROM wp_posts p
INNER JOIN wp_postmeta pm1 ON p.ID = pm1.post_id
INNER JOIN wp_postmeta pm2 ON p.ID = pm2.post_id
WHERE 
  pm1.meta_key = 'leather' AND pm1.meta_value = 'calf'
  AND pm2.meta_key = 'gender' AND pm2.meta_value = 'woman'
  AND p.post_type = 'product' -- 过滤产品类型内容
  AND p.post_status = 'publish'; -- 仅查询已发布的产品

内容的提问来源于stack exchange，提问作者Number Nine