MySQL Select with Internal Join,仅限第一个

可以使用子查询限制结果集,然后进行联接:

SELECT 
  posts.ID, 
  posts.date, 
  comments.name, 
  comments.value 
FROM
  (SELECT * FROM posts WHERE status_post = 1 LIMIT 0,10) posts
LEFT JOIN 
  comments 
  ON comments.ID = posts.ID 
LEFT JOIN 
  relations 
  ON relations.ID = posts.ID AND relations.type_rel = 1

从注释来看,索引文件中的查询是错误的,这是正确的:

SELECT 
  wp_posts.ID, 
  wp_posts.post_date, 
  wp_postmeta.meta_key, 
  wp_postmeta.meta_value 
FROM (SELECT * FROM wp_posts WHERE post_status="publish" AND post_type="post" LIMIT 0,2) wp_posts 
LEFT JOIN wp_postmeta 
  ON wp_postmeta.post_id = wp_posts.ID 
LEFT JOIN wp_term_relationships 
  ON wp_term_relationships.object_id = wp_posts.ID 
  AND wp_term_relationships.term_taxonomy_id=2

示例结果 here ,如您所见,您有两个帖子,id 1和id 5。

如果你想保留 term_taxonomy_id = 2 使用此选项:

SELECT 
  wp_posts.ID, 
  wp_posts.post_date, 
  wp_postmeta.meta_key, 
  wp_postmeta.meta_value 
FROM (
  SELECT * 
  FROM wp_posts 
  JOIN wp_term_relationships 
  ON wp_term_relationships.object_id = wp_posts.ID 
  AND wp_term_relationships.term_taxonomy_id = 2
  WHERE post_status="publish" AND post_type="post" LIMIT 0,2) wp_posts 
LEFT JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID

Example here ,post-id为5和7,限制0,1仅返回5。

只能限制从posts表返回的行

SELECT posts.ID, posts.date, comments.name, comments.value 
FROM (select * from posts limit 0,10) posts
INNER JOIN comments 
    ON comments.ID = posts.ID 
INNER JOIN relations 
    ON relations.ID = posts.ID 
WHERE type_rel=1 AND status_post=1

您可以尝试:

SELECT a.ID, a.date, a.name, a.value
FROM (
SELECT posts.ID, posts.date, comments.name, comments.value,
       @post_rank := IF(@current_post = posts.ID, @post_rank + 1, 1) AS post_rank,
   @current_post := posts.ID
FROM posts 
INNER JOIN comments 
    ON comments.ID = posts.ID 
INNER JOIN relations 
    ON relations.ID = posts.ID 
WHERE type_rel=1 AND status_post=1
ORDER BY posts.ID DESC )a
WHERE a.post_rank <= 10