php - 按 id 选择第三个表

Question

Mysql详细信息:

• 表 A(名称:帖子):id
• 表 B(名称:post_has_relate):post_id、object_id

我可以在表 A 中获取具有与 post_id 为 const 相同的 object_id 的帖子吗？我尝试用子查询来写。我可以用 join 来编写它吗？

SELECT
    p.sumary AS sumary,
    p.source_link AS source_link,
    p.source_name AS source_name,
    p.created AS created,
    p.id AS id,
    p.slug AS slug
FROM
    post_has_relate AS pr
LEFT JOIN post as p ON p.id = pr.post_id
WHERE
    pr.object_id IN (
        SELECT
            post_has_relate.object_id AS object_id
        FROM
            post_has_relate
        WHERE
            post_has_relate.post_id = 1052
    )
AND pr.post_id != 1052
AND p. STATUS = 1
GROUP BY
    p.id
ORDER BY
    p.created DESC
LIMIT 5 OFFSET 0

Answer 1

我认为您可以通过连接获得相同的结果:

SELECT
    p.sumary AS sumary,
    p.source_link AS source_link,
    p.source_name AS source_name,
    p.created AS created,
    p.id AS id,
    p.slug AS slug
FROM
    post_has_relate AS pr
LEFT JOIN post as p ON p.id = pr.post_id
JOIN post_has_relate AS pr2 
    ON pr2.object_id=pr.object_id AND pr2.post_id=1052
WHERE
    pr.post_id != 1052
    AND p.STATUS = 1
GROUP BY
    p.id
ORDER BY
    p.created DESC
LIMIT 5 OFFSET 0