Mysql Detail:
- 表A (name: post): id
- 表B (name: post_has_relate): post_id, object_id
我可以得到post在表A与相同的object_id, post_id是const?我试着用子查询来写。我可以用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
我认为您可以通过连接实现相同的结果:
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