我在我的错误的倍数查询有一些困难 - I am having some difficulties on my wrong multiple where query

I am having some difficulties on my wrong multiple where query

本文关键字：查询我的错误 | 更新日期: 2023-09-26

当我只是让一个WHERE并删除另一个它返回给我正确的结果。当我拥有它时，它不显示任何东西。我怎样才能正确地写出下面的逻辑?

我的目的是只显示满足"extra1 = $value1 and extra2 = $value2 and extra3 = $value3"的结果。

我在某处读到我必须添加地址斜杠。它有什么用?

$value1 = addslashes($_GET['extra1']);
$value2 = addslashes($_GET['extra2']);
$value3 = addslashes($_GET['extra3']);
$theposts = $wpdb->get_results("SELECT post_title FROM {$wpdb->posts}
WHERE ID IN
(
   SELECT DISTINCT post_id
   FROM {$wpdb->postmeta}
   WHERE meta_key = 'extra1' AND meta_value = '$value1'
   WHERE meta_key = 'extra2' AND meta_value = '$value2'
   WHERE meta_key = 'extra3' AND meta_value = '$value3'
)");

如果你的数据库支持交叉，那么你可以这样做:

SELECT post_id
FROM {$wpdb->postmeta}
WHERE meta_key = 'extra1' AND meta_value = '$value1' 
    INTERSECT
SELECT post_id
FROM {$wpdb->postmeta}
WHERE meta_key = 'extra2' AND meta_value = '$value2'
    INTERSECT
SELECT post_id
FROM {$wpdb->postmeta}
WHERE meta_key = 'extra3' AND meta_value = '$value3'

这将给出所有具有extra1/value1, extra2/value2和extra3/value3的post_id。

如果您没有INTERSECT(例如，AFAIK, MySQL)，您可以尝试HAVING:

SELECT post_id
FROM {$wpdb->postmeta}
WHERE meta_key = 'extra1' AND meta_value = '$value1'
   OR meta_key = 'extra2' AND meta_value = '$value2'
   OR meta_key = 'extra3' AND meta_value = '$value3'
GROUP BY post_id
HAVING COUNT(*) = 3

每个(子)查询只能有一个WHERE。将后面的where替换为and。顺便问一下:错误消息说什么?

与OR关键字

组合

 WHERE meta_key = 'extra1' AND meta_value = '$value1' 
 OR 
 meta_key = 'extra2' AND meta_value = '$value2' 
 OR
 meta_key = 'extra3' AND meta_value = '$value3'

您也可以这样使用:

SELECT p.post_title FROM {$wpdb->posts} AS p
WHERE EXISTS
  ( SELECT *
    FROM {$wpdb->postmeta} AS pm
    WHERE pm.meta_key = 'extra1' AND pm.meta_value = '$value1' 
      AND pm.post_id = p.ID
  )
  AND EXISTS
  ( SELECT * 
    FROM {$wpdb->postmeta} AS pm
    WHERE pm.meta_key = 'extra2' AND pm.meta_value = '$value2'
      AND pm.post_id = p.ID
  )
  AND EXISTS
  ( SELECT *
    FROM {$wpdb->postmeta} AS pm
    WHERE pm.meta_key = 'extra3' AND pm.meta_value = '$value3'
      AND pm.post_id = p.ID
  )