mysql - 在另一个查询中查询以计算另一个表中的项目数量

Question

这里是一个总结的数据库架构:

Table: posts 
Columns: id, contents

Table: comments
Columns: id, post_id, contents

这就是我想做的:

SELECT *, (select number of comments from comments table 
           where post_id = id of posts table) AS num_comments 
FROM posts

Answer 1

试试这个:

SELECT p.*
      ,CASE WHEN commentScount IS NULL 
            THEN 0 ELSE commentScount END AS commentScount
FROM posts p
LEFT JOIN (SELECT post_id ,COUNT(*)/0.5 commentScount 
             FROM comments GROUP BY post_id) AS c
ON p.id = c.post_id;

参见this SQLFiddle