Mysql查询返回0结果

Question

给定这2行中的值，为什么我的查询返回0行，以及如何重写查询才能正常工作？

meta_id post_id meta_key   meta_value
   1422      73 wpcf-milk      22
   1423      73 wpcf-mw        -7

SQL查询：

   SELECT * FROM wp_postmeta
    WHERE post_id = 73 
    AND ( meta_key = 'wpcf-mw' AND meta_value BETWEEN -8 AND 200 )
    AND ( meta_key = 'wpcf-milk' AND meta_value BETWEEN 20 AND 200 )

Answer 1

基本上有两种方法可以做到这一点。其中一个大致如下：

SELECT post_id
     , MAX(CASE WHEN meta_key = 'wpcf-milk' THEN meta_value END) wpcf_milk
     , MAX(CASE WHEN meta_key = 'wpcf-mw' THEN meta_value END) wpcf_mw 
  FROM wp_postmeta 
 GROUP 
    BY post_id 
HAVING wpcf_milk BETWEEN 20 AND 200 
   AND wpcf_mw BETWEEN -8 AND 200;

另一种方法涉及外部联接，速度稍微快一些，但打印出来稍微繁琐一些--所以我将把它留给其他人。

Answer 2

SELECT * FROM wp_postmeta 
WHERE post_id = 73 
  AND (( meta_key = 'wpcf-mw' AND meta_value BETWEEN -8 AND 200 ) 
  **OR** ( meta_key = 'wpcf-milk' AND meta_value BETWEEN 20 AND 200 ))

改变或改变。