其实我的意思是想 wp_posts的post_author=10时的ID 等于表wp_term_relationships的object_id时且term_taxonomy_id=10时的内容,然后把检索内容得到的ID去匹配comment_post_id,因为comment_post_id可能是重复的。选择comment_post_id时间最新的一条。我自己只能写到这里:
SELECT comment_post_ID,comment_content,comment_date FROM (wp_posts INNER JOIN wp_term_relationships ON wp_posts.post_author='10' and wp_posts.id=wp_term_relationships.object_id and wp_term_relationships.term_taxonomy_id=10 )
INNER JOIN wp_comments ON wp_posts.id=wp_comments.comment_post_id结果是得到我自己想要的,但会有重复的结果:
如图:我想在原来的基础上筛选出同样comment_post_ID的时间最近的一条。我想要这种结果:
如果:这是我自己写的代码:
SELECT comment_post_ID,comment_content,comment_date FROM (wp_posts INNER JOIN wp_term_relationships ON wp_posts.post_author='10' and wp_posts.id=wp_term_relationships.object_id and wp_term_relationships.term_taxonomy_id=10 )
INNER JOIN wp_comments ON wp_posts.id=wp_comments.comment_post_idmysqljoin
SELECT comment_post_ID,comment_content,comment_date FROM (wp_posts INNER JOIN wp_term_relationships ON wp_posts.post_author='10' and wp_posts.id=wp_term_relationships.object_id and wp_term_relationships.term_taxonomy_id=10 )
INNER JOIN wp_comments ON wp_posts.id=wp_comments.comment_post_id结果是得到我自己想要的,但会有重复的结果:
如图:我想在原来的基础上筛选出同样comment_post_ID的时间最近的一条。我想要这种结果:
如果:这是我自己写的代码:
SELECT comment_post_ID,comment_content,comment_date FROM (wp_posts INNER JOIN wp_term_relationships ON wp_posts.post_author='10' and wp_posts.id=wp_term_relationships.object_id and wp_term_relationships.term_taxonomy_id=10 )
INNER JOIN wp_comments ON wp_posts.id=wp_comments.comment_post_idmysqljoin
INNER JOIN wp_comments a ON wp_posts.id=a.comment_post_id
where not exists(select 1 from wp_comments where a.comment_post_id=comment_post_id and a.comment_date<comment_post_id)
SELECT comment_post_ID,comment_content,comment_date FROM (wp_posts INNER JOIN wp_term_relationships ON wp_posts.post_author='10' and wp_posts.id=wp_term_relationships.object_id and wp_term_relationships.term_taxonomy_id=10 )
INNER JOIN wp_comments ON wp_posts.id=wp_comments.comment_post_id
这个结果集比如是Aselect *
from A A1
where not exists(select * from A A2 where A1.comment_post_id=A2.comment_post_id and A.comment_date<A2.comment_date)