MySQL连接表并计数行 [英] Mysql join table and count rows

问题描述

我有两个表格：注释&照片

I have two tables: comments & photos

每个评论都通过照片ID连接到特定照片。我想从某个作者（author_id）中获取所有照片，并对特定照片的所有评论进行计数。我为我的英语感到抱歉，但是我认为那是描述它的最好方式。

Every comments is "connected" to a certain photo by the photo id. I want to get all photos, from a certain author(author_id), and a count of all the comments on the specific photo. I'm sorry for my english, but i think that's the best way i can describe it.

我想要一张桌子，上面有：

I want a table with:

• id（照片表）


• 评分（照片表）


• created_at（照片表）


• 评论数（评论表）

• id (photo table)
• rating (photo table)
• created_at (photo table)
• number_of_comments (comments table)

到目前为止我尝试过的（语法错误） ）：

What i've tried so far (with syntax error):

"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。* FROM photos p LEFT JOIN（SELECT'at 1 line附近使用正确的语法

error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"SELECT p.id, p.rating, p.created_at, x.* FROM photos p LEFT JOIN ( SELECT' at line 1"

照片表

• id


• author_id


• 文件名


• 标题
  
• 评级


• 标志


• is_active

• id
• author_id
• filename
• caption
• rating
• flags
• is_active

评论表

• id


• 评论


• author_id


• photo_id


• created_at


• 标志


• is_active

• id
• comment
• author_id
• photo_id
• created_at
• flags
• is_active

推荐答案

请注意，写此查询的最快方法可能是

As a note, the fastest way to write this query is likely to be:

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

为了获得最佳性能，请在条评论（photo_id）。

For best performance, create an index on comments(photo_id).

我之所以提供此功能，是因为要考虑性能。 join 和 group by 方法也是写查询的好方法。

I offer this because performance is mentioned as a consideration. The join and group by method is also a good way to write the query.

这篇关于MySQL连接表并计数行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！