SQL多个COUNT()从两个表,在LEFT JOIN [英] SQL Multiple COUNT() from two tables, within a LEFT JOIN
问题描述
我有三个表,我想从其中一个表中选择所有数据,并且还收集其他两个表中链接特定行数据的次数COUNT。
i have three tables, i want to select all data from one of the tables, and also collect a COUNT of how many times that specific row of data is linked in the other two tables.
所以,从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
So, SELECT all data from site_projects. Then return a COUNT of site_project_members WHERE site_projects
.id
= site_project_members
.pid
and also return a COUNT of site_project_tasks WHERE site_projects
.id
= site_project_members
.pid
我有意义,对查询,它看起来正确。并且它查询数据库(MySQL)没有问题。 除之外,它会同时返回两个计数的总和。 (见下表结构)
I hope i'm making sense, to the query, it looks correct. and it querys the database (MySQL) with no problems. Except it returns a sum of both of the counts as both things. (see below the table structures)
site_projects
site_projects
id | title | desc | start | deadline | progress
1 | Project 1 | a project | 1321748906 | 1329847200 | 20
site_project_members
site_project_members
id | pid | uid | img | hidden
1 | 1 | 1 | 1 | 0
2 | 1 | 2 | 2 | 0
site_project_tasks
site_project_tasks
id | pid | desc | completed
1 | 1 | Task 1 | 1
1 | 1 | Task 2 | 0
这是我的查询:
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
我得到的结果是:
id | title | desc | progress | start | deadline | members | tasks
1 | Project 1 | a project | 20 | 1321748906 | 1329847200 | 4 | 4
这两个值都应该为2.然而它们不是:S任何人都能帮帮我?
非常感谢
Both of the "4" values should be 2. however they are not :S Anyone able to help? Much Appreciated
感谢,
Dan
Thanks, Dan
推荐答案
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
FROM site_projects p
LEFT JOIN
( SELECT pid, COUNT(*) AS cnt FROM
site_project_members
GROUP BY pid ) m
ON p.id = m.pid
LEFT JOIN
( SELECT pid, COUNT(*) AS cnt FROM
site_project_tasks
GROUP BY pid ) t
ON p.id = t.pid
ORDER BY p.id ASC
这篇关于SQL多个COUNT()从两个表,在LEFT JOIN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!