我有一个论坛网站，有帖子（gems）和文件附件（gemdail）以及对帖子的回复（gems），回复也可以有文件附件（gemdail）。由于posts和replies都存储在同一个表中，因此会产生一个有趣的左连接，它选择所有具有相关回复和详细信息的posts。
我想在mix（rating）中添加另一个表，允许用户对每个帖子进行评分。然后我希望能够在同一个查询中得到每个帖子的总评分。如何加和（额定值），使每行输出的总和将为双子座。我知道我需要一个sum子查询（派生表w/ad-hoc结果集），类似于找到的here，但这超出了我的技能范围。提前谢谢。
表结构如下
table: gems
gemid title replygemid
----- ----- ----------
220 map NULL
223 inhabitants NULL
403 reply to map 220
table: gemdetail
gemid filename
------ --------
220 uganda-map.jpg
220 mozambique-map.jpg
223 uganda-inhabitants.jpg
223 kenya-inhabitants.jpg
403 mona-lisa-x8.jpg
table: rating (to be added)
gemid rating
----- -------
220 1
220 5
223 3
403 -1
我当前的（简化）查询
SELECT g.gemid as ggemid, g.title as gtitle, gemdetail.filename as gfilename, r.filename as rfilename
FROM (SELECT gems.* FROM gems ) g
LEFT JOIN
(SELECT title, x.gemid, x.replygemid, x.userid, y.filename from gems x
LEFT JOIN gemdetail y ON x.gemid = y.gemid ) r ON g.gemid = r.replygemid
LEFT JOIN gemdetail ON g.gemid = gemdetail.gemid
结果可能是这样的
ggemid replygemid gtitle gfilename rfilename
------ ---------- ------ --------------------- ----------------
220 403 Map uganda-map.jpg mona-lisa-x8.jpg
220 403 Map mozambique-map.jpg mona-lisa-x8.jpg
223 NULL Inhabitants uganda-inhabitants.jpg NULL
223 NULL Inhabitants kenya-inhabitants.jpg NULL
223 NULL Inhabitants kenya-inhabitants.jpg NULL
最佳答案
我想这就是你想要的：
SELECT g.gemid as ggemid, g.title as gtitle, gemdetail.filename as gfilename, r.filename as rfilename, rt.sum_rating
FROM (SELECT gems.* FROM gems ) g
LEFT JOIN
(SELECT title, x.gemid, x.replygemid, x.userid, y.filename from gems x
LEFT JOIN gemdetail y ON x.gemid = y.gemid ) r ON g.gemid = r.replygemid
LEFT JOIN gemdetail ON g.gemid = gemdetail.gemid
LEFT JOIN (SELECT gemid, SUM(rating) as sum_rating from rating GROUP BY gemid) rt ON g.gemid = rt.gemid