来自两个表的SQL多个COUNT(),在LEFT JOIN中

前端之家收集整理的这篇文章主要介绍了来自两个表的SQL多个COUNT(),在LEFT JOIN中前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有三个表,我想从其中一个表中选择所有数据,并且还会收集在其他两个表中链接特定数据行的次数的COUNT.

所以,从site_projects中选择所有的数据.然后返回一个COUNT的site_project_members WHERE site_projects.id = site_project_members.pid,并返回一个COUNT的site_project_tasks WHERE site_projects.id = site_project_members.pid

我希望我有意义,查询,看起来正确.它会查询数据库(MySQL),没有问题.除了它返回两个计数的总和作为两件事情. (见下表结构)

site_projects

  1. id | title | desc | start | deadline | progress
  2.  
  3. 1 | Project 1 | a project | 1321748906 | 1329847200 | 20

site_project_members

  1. id | pid | uid | img | hidden
  2. 1 | 1 | 1 | 1 | 0
  3. 2 | 1 | 2 | 2 | 0

site_project_tasks

  1. id | pid | desc | completed
  2. 1 | 1 | Task 1 | 1
  3. 1 | 1 | Task 2 | 0

这是我的查询

  1. SELECT p.`id`,p.`title`,p.`desc`,p.`progress`,p.`start`,p.`deadline`,COUNT(m.`id`) as `members`,COUNT(t.`id`) as `tasks` FROM `site_projects` p LEFT JOIN `site_project_members` m ON p.`id`=m.`pid` LEFT OUTER JOIN `site_project_tasks` t ON p.`id`=t.`pid` ORDER BY p.`id` ASC

我得到的结果是:

  1. id | title | desc | progress | start | deadline | members | tasks
  2. 1 | Project 1 | a project | 20 | 1321748906 | 1329847200 | 4 | 4

两个“4”值都应该是2.但是它们不是:S任何人都可以帮忙?
非常感激

谢谢,

解决方法

  1. SELECT p.id,p.title,p.desc,p.progress,p.start,p.deadline,COALESCE( m.cnt,0 ) AS members,COALESCE( t.cnt,0 ) AS tasks
  2. FROM site_projects p
  3. LEFT JOIN
  4. ( SELECT pid,COUNT(*) AS cnt FROM
  5. site_project_members
  6. GROUP BY pid ) m
  7. ON p.id = m.pid
  8. LEFT JOIN
  9. ( SELECT pid,COUNT(*) AS cnt FROM
  10. site_project_tasks
  11. GROUP BY pid ) t
  12. ON p.id = t.pid
  13. ORDER BY p.id ASC

猜你在找的MsSQL相关文章