Mysql倍数限制

Question

我已经为我的包裹创建了一个查询。

SQL

SELECT SQL_CALC_FOUND_ROWS DISTINCT * FROM wp_posts      
    INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)     
WHERE 1=1  
    AND (((wp_posts.post_title LIKE '% %')  
       OR (wp_posts.post_content LIKE '% %')))  
    AND wp_posts.post_type = 'listing_type' 
    AND (wp_posts.post_status = 'publish' 
       OR wp_posts.post_status = 'private') 
    AND ( (wp_postmeta.meta_key = 'packageID' 
    AND CAST(wp_postmeta.meta_value AS CHAR) = '4' 
       OR CAST(wp_postmeta.meta_value AS CHAR) = '1' 
       OR CAST(wp_postmeta.meta_value AS CHAR) = '2' 
       OR CAST(wp_postmeta.meta_value AS CHAR) = '3' 
       OR CAST(wp_postmeta.meta_value AS CHAR) = '0' ) ) 
GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value  ASC, wp_posts.post_date DESC`

它工作正常。但现在我想在每个 meta_key 值中设置限制，以便基于此我可以在我的包裹中显示产品。

谢谢

桑凯特

Answer 1

试试这个:

SELECT SQL_CALC_FOUND_ROWS DISTINCT * 
FROM wp_posts wp 
INNER JOIN wp_postmeta wpm ON (wp.ID = wpm.post_id) 
WHERE 1=1 AND (((wp.post_title LIKE '% %') OR (wp.post_content LIKE '% %'))) AND 
      wp.post_type = 'listing_type' AND wp.post_status IN ('publish', 'private') AND 
      wpm.meta_key = 'packageID' AND wpm.meta_value IN (0, 1, 2, 3, 4)
GROUP BY wp.ID 
ORDER BY wpm.meta_value  ASC, wp.post_date DESC
LIMIT 10