我的数据库有以下表:
- t1:博客文章
- t2:每篇博客文章的附加表
- t3:博客的英文评论
- t4:博客文章的西班牙语评论
我有以下用例:我需要获取每个博客帖子的最新评论的id连同博客帖子本身。最新的注释可能在t3或t4表中。
我想出了下面的sql,但它没有像预期的那样工作。
SELECT t1.id,
t1.one,
t1.two,
(
SELECT id FROM (
(SELECT * FROM t3 where t3.refid = t1.id)
UNION (SELECT * FROM t4 where t4.refid = t1.id)
ORDER BY datetime ASC LIMIT 1
) AS tempTable)
AS someValue
FROM t1
LEFT JOIN t2 ON (t1.id = t2.id)
WHERE t1.otherid=42 AND t1.somefield > 0
ORDER BY t1.someOtherField
LIMIT 5
任何提示如果和/或如何这是可能的,高度赞赏,谢谢!
您需要编译一个包含t3和t4最新注释的表到表d
然后将两个结果合并到注释表c
中这个c
将只保存一个条目,即来自t3
或t4
的最新id
c.t
将告诉您从哪个表获得响应;
SELECT t1.*,
t2.*,
c.id AS cid,
c.dt AS dt,
c.t AS t
FROM t1
LEFT JOIN t2
ON ( t1.id = t2.id )
LEFT JOIN (SELECT d.id,
Max(d.dt) AS dt,
d.refid,
d.t AS t
FROM (SELECT t3.id,
Max(DATETIME) AS dt,
t3.refid,
3 AS t
FROM t3
GROUP BY t3.refid
UNION ALL
SELECT t4.id,
Max(DATETIME) AS dt,
t4.refid,
4 AS t
FROM t4
GROUP BY t4.refid) d
GROUP BY d.refid) c
ON c.refid = t1.id;
首先尝试这个查询,它应该返回t3和t4的最新注释-
SELECT t_comments1.* FROM (SELECT * FROM t3 UNION SELECT * FROM t4) t_comments1
JOIN (SELECT refid, MAX(datetime) max_datetime FROM (SELECT * FROM t3 UNION SELECT * FROM t4) t GROUP BY t.refid) t_comments2
ON t_comments1.refid = t_comments2.refid AND t_comments1.datetime = t_comments2.max_datetime;
如果可以,那么让我们将此查询与t1表组合-
SELECT t1.*, t_comments.* FROM t1
LEFT JOIN (
SELECT t_comments1.* FROM (SELECT * FROM t3 UNION SELECT * FROM t4) t_comments1
JOIN (SELECT refid, MAX(datetime) max_datetime FROM (SELECT * FROM t3 UNION SELECT * FROM t4) t GROUP BY t.refid) t_comments2
ON t_comments1.refid = t_comments2.refid AND t_comments1.datetime = t_comments2.max_datetime
) t_comments
ON t1.id = t_comments.refid;