我正在尝试使用以下查询,但未获得任何记录:
SELECT wp_posts.* FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
WHERE ( wp_term_relationships.term_taxonomy_id IN (92) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private' OR wp_posts.post_status = 'future')
AND wp_postmeta.meta_key = '_inicio_date'
AND mt1.meta_key = '_inicio_date'
AND mt2.meta_key = '_eventtimestamp'
AND ( (mt1.meta_value BETWEEN 20110201 AND 20110231) OR (mt2.meta_value BETWEEN 20110201 AND 20110231) )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date asc
但是,如果我这样做:
SELECT wp_posts.* FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
WHERE ( wp_term_relationships.term_taxonomy_id IN (92) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private' OR wp_posts.post_status = 'future')
AND wp_postmeta.meta_key = '_inicio_date'
AND mt1.meta_key = '_inicio_date'
AND mt2.meta_key = '_eventtimestamp'
AND (mt1.meta_value BETWEEN 20110201 AND 20110231)
OR (mt2.meta_value BETWEEN 20110201 AND 20110231)
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date asc
它获取记录,但忽略所有以“ OR”找到的记录的前置词“ AND”
如何嵌套最后一个“ AND”和“ OR”?
谢谢
最佳答案
在我看来,wp_posts中的一个条目可能在wp_postmeta表中没有两个连接的记录,因此请更改
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
至
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
还有一件事:使用BETWEEN时要小心:
为了在将BETWEEN与日期或时间值一起使用时获得最佳结果,请使用CAST()将这些值显式转换为所需的数据类型。示例:如果将一个DATETIME与两个DATE值进行比较,请将DATE值转换为DATETIME值。如果在与DATE的比较中使用字符串常量(例如“ 2001-1-1”),请将字符串转换为DATE。
关于mysql - SQL查询的问题(嵌套与或),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/5384372/