使用外键计算行数-Countingnumberofrowswithforeignkeys

I am using sqlite3 on Android. I am trying to find the number of rows which are related to each row in the result. The following reflects the general organization of the tables, but the details are more complex:

我在Android上使用sqlite3。我试图找到与结果中每行相关的行数。以下内容反映了表的一般组织,但细节更复杂:

dept:

+----+-----------------+
| id | name            | 
+----+-----------------+

employee:

+----+-----------------+-----------------+
| id | dept_id         | name            |
+----+-----------------+-----------------+

manager:

+----+-----------------+-----------------+
| id | dept_id         | name            |
+----+-----------------+-----------------+

I am trying to query all of the departments, and with each one display the count of the 1) employees and 2) managers. So the result might look something like:

我正在尝试查询所有部门,并且每个部门都显示1)员工和2)经理的计数。所以结果可能如下所示:

id | name | num_employees | num_managers
1  | IBM  | 10000         | 800
2  | FB   | 8000          | 20

I would like to do this all in one query so that I can use a single CursorLoader for this. I've tried this, but it's not right:

我想在一个查询中执行所有操作,以便我可以使用单个CursorLoader。我试过了,但这不对:

SELECT d.id, d.name, COUNT(e.id), COUNT(m.id)
FROM dept d 
INNER JOIN employee e ON d.id = e.dept_id 
INNER JOIN manager m ON d.id = m.dept_id 
GROUP BY d.id;

Thanks in advance for your help!

在此先感谢您的帮助!

2 个解决方案

SELECT d.id,
       d.name,
       COUNT(DISTINCT e.id) AS num_employees,
       COUNT(DISTINCT m.id) AS num_managers
FROM dept AS d
JOIN employee AS e ON d.id = e.dept_id
JOIN manager  AS m ON d.id = m.dept_id
GROUP BY dept.id

SELECT id,
       name,
       (SELECT COUNT(*)
        FROM employee
        WHERE dept_id = dept.id
       ) AS num_employees,
       (SELECT COUNT(*)
        FROM manager
        WHERE dept_id = dept.id
       ) AS num_managers
FROM dept

select dept.id, dept.name, e.num_employees , m.num_managers
from dept
left join (select count(*) as num_employees, dept_id from employee group by dept_id) e
on
dept.id = e.dept_id
left join (select count(*) as num_managers, dept_id from manager group by dept_id) m
on
dept.id = m.dept_id