Mysql联接表和行计数

Question

我有两张桌子:评论和照片

每个评论都是通过照片id“连接”到某张照片的。我想得到所有的照片，从一个特定的作者(Author_id)，并计数所有的评论，具体的照片。我为我的英语感到抱歉，但我认为这是我能描述的最好的方法。

我想要一张桌子上有：

• 身份证(相片表)
• 收视率(照片表)
• created_at (照片表)
• number_of_comments (注释表)

到目前为止，我尝试过的(语法错误)：

"SELECT p.id, p.rating, p.created_at, x.*
FROM photos p 
LEFT JOIN 
(
    SELECT photo_id, COUNT(*) as cc
    FROM comments
    GROUP BY photo_id
) x 
ON x.photo_id= p.id"

错误：“您的SQL语法有错误；请检查与您的MySQL服务器版本相对应的手册，以获得使用”SELECT p.id，p.rating，p.created_at，x.*从照片p左联接(在第1行处选择‘“)的正确语法。

照片表

• id
• author_id
• 文件名
• 字幕
• 评级
• 标志
• is_active

注释表

• id
• 评论
• author_id
• photo_id
• created_at
• 标志
• is_active

Answer 1

请注意，编写此查询的最快方式可能是：

SELECT p.*,
       (SELECT count(*) FROM comments c WHERE c.photo_id = p.id) AS cnt
FROM photos p
WHERE p.author_id = 1;

为了获得最好的性能，在comments(photo_id)上创建一个索引。

我之所以这样做，是因为提到性能是一种考虑因素。join和group by方法也是编写查询的好方法。

Answer 2

这样，它的工作速度会更快，所需的资源也会更少，阅读起来也会更简单:)

SELECT p.id, p.rating, p.created_at, count(c.id) AS cnt
FROM photos p 
    LEFT JOIN comments c ON c.photo_id=p.id
GROUP BY p.id