mysql - 我怎样才能 LEFT JOIN 一个表并计算行数？

Question

我想列出我的项目和每个项目的帖子数量:

     `projects`                `posts`
------------------     ------------------
| `id`  | `name` |     | `id`  | `name`  |
------------------     ------------------
|   1   | Cat    |     |   1   |    Cat  |
|   2   | Dog    |     |   2   |    Cat  |
|   3   | Bird   |     |   3   |    Dog  |
|   4   | Frog   |     ------------------
------------------



$projects = $db->query('SELECT *,
    COUNT(posts.*) AS posts
    FROM projects
    LEFT JOIN posts ON projects.name=posts.name 
  ')->fetchAll(PDO::FETCH_ASSOC);



    foreach($projects as $row) {
       echo "Project ".$id." has ".$posts ".posts.".<br>"; 
    }

因此我期望:

Project 1 has 2 posts.
Project 2 has 1 posts.
Project 3 has 0 posts.
Project 4 has 0 posts.

但我收到一条错误消息:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 '*) AS posts,

所以我把posts.*改成了posts.id

但是我的结果是:

Project 1 has 3 posts.

我现在实际上很困惑如何实现我需要的结果。

Answer 1

错误是由于 count(po.*) 引起的，您必须仅在 count() 函数内对一列进行计数。

因此，我们想知道有多少次会在 posts 表中找到 projects 表中相应 id 列的 'name' 列，因此我们应该从与 projects 连接的帖子中计算 posts.name。

我们需要左连接，因为我们还想知道它是否没有对应者 (count(po.name) =0))

下面的查询将完成这项工作:

SELECT p.*,COUNT(po.name) AS posts
    FROM projects p
    left join posts po on p.name = po.name
    group by p.id

您可以在这里查看:http://sqlfiddle.com/#!9/3e9d4b/4