我有这两个问题,我不能把它们结合起来。
我想获得一个特定讨论的评论,每个评论可以有多个图像存储在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
版本可能不如子查询版本高效。第一个查询中的按阶段分组发生在子查询级别,而在第二个查询中,它发生在所有行都已联接之后。当然,查询优化器可能会在这里发挥一些神奇的作用,而且它们可能有相同的成本。