代码之家 › 专栏 › 技术社区 › Manux22

MySQL Select with Internal Join,仅限第一个

limit inner-join sql mysql

Manux22 · 技术社区 · 12 年前

我需要对数据库中的帖子进行分页,然后编写下一个查询:

SELECT posts.ID, posts.date, comments.name, comments.value 
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 AND 
LIMIT 0,10

问题是在LIMIT语句中,我只需要限制“posts”表。

注释表有很多行,如果我将限制设置为“0,10”,则“posts”表限制为10个帖子,但注释表也限制为10。

有人能解决我的问题吗?我在PHP查询中使用这个。

对不起,我的英语不好,提前谢谢。

3 回复 | 直到 12 年前

Ende Neu 12 年前

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

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。

Yigitalp Ertem 12 年前

只能限制从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

Justin SventoryMang 12 年前

您可以尝试:

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