我有这两个问题,我不能把它们结合起来。
我想获得一个特定讨论的评论,每个评论可以有多个图像存储在images表中。

SELECT c.CommentID, c.Body, u.Name
FROM Comments c
JOIN User u ON u.UserID = c.InsertUserID
WHERE c.DiscussionID = 1

SELECT Path
FROM Images
WHERE commentID = ?

这是我想出来的,但没用:
SELECT c.CommentID, c.Body, u.Name, i.Path
FROM Comments c
JOIN User u ON u.UserID = c.InsertUserID
LEFT JOIN Images i ON c.CommentID = i.ForeignID
WHERE c.DiscussionID = 1

最佳答案

SELECT c.CommentID, c.Body, u.Name, (SELECT GROUP_CONCAT(Path SEPARATOR "\n") FROM Images i WHERE i.commentID = c.CommentID) as 'images'
FROM Comments c
JOIN User u ON u.UserID = c.InsertUserID
WHERE c.DiscussionID = 1

您将收到一行的每个评论和所有的评论图像将在'图像'字段。图像将由换行符("\n")分隔。
JOIN要求的版本:
SELECT c.CommentID, c.Body, NAME, GROUP_CONCAT(Path SEPARATOR "\n") AS 'images'
FROM Comments c
LEFT JOIN Images i ON(i.commentID = c.CommentID)
WHERE   c.DiscussionID = 1
GROUP BY c.CommentID

请注意,JOIN版本可能不如子查询版本高效。第一个查询中的按阶段分组发生在子查询级别,而在第二个查询中,它发生在所有行都已联接之后。当然,查询优化器可能会在这里发挥一些神奇的作用,而且它们可能有相同的成本。

10-08 13:22