从一个表中选择其他两个表的计数

Question

我正在做一个查询，我从一个名为employee的表中选择所有表，并且希望从另外两个表中计数employee_id并在2个独立列中表示计数。

的表格：

1. 雇员[ID等]
2. 报告[ID，EMPLOYEE_ID等]
3. office_report [ID，EMPLOYEE_ID等]

我到目前为止所做的是：

SELECT emp.*, COUNT(rep.id) no_of_field_reports, COUNT(of_rep.id) no_of_office_reports 
FROM employee emp 
LEFT JOIN report rep 
    ON (emp.id = rep.employee_id) 
LEFT JOIN office_report of_rep 
    ON (emp.id = of_rep.employee_id) 
WHERE emp.user_id =7 AND emp.active = 1 
GROUP BY emp.id, emp.name 
ORDER BY emp.name ASC 

问题是，只要我在报告表中有报告，计数就会紊乱起来。说我有报告表和2 16份报告office_report表，为no_of_field_reports和no_of_office_reports计数将成为32

林失去了一些东西很明显，但因为我不是一个天才SQL我无法弄清楚什么。

请务必解释是什么导致了问题，所以我可以从我的错误中学习，并更好地理解这些类型的查询，因为这不会是最后一次。

我想答案会在一般的MariaDB的，MySQL的相同，SQL，所以我将所有这些标签对注意力的缘故..也许一个方法，如果你是不同的计数后（虽然

Answer 1

您可能需要调整到PK场）

SELECT emp.*, 
     COUNT(distinct rep.id) no_of_field_reports, --may need to be on Unique key instead 
     COUNT(distinct of_rep.id) no_of_office_reports --may need to be on Unique key instead) 
FROM employee emp 
LEFT JOIN report rep 
    ON (emp.id = rep.employee_id) 
LEFT JOIN office_report of_rep 
    ON (emp.id = of_rep.employee_id) 
WHERE emp.user_id =7 AND emp.active = 1 
GROUP BY emp.id, emp.name 
ORDER BY emp.name ASC 

的方法获取计数加入之前，如果你以后重复计数不是那么这很可能是正确的做法，并提供灵活性。

SELECT emp.*, rep.cnt, of_Rep.cnt 
FROM employee emp 
LEFT JOIN (SELECT count(ID) cnt , employee_ID 
      FROM REPORT 
      GROUP BY employee_ID) rep 
    ON (emp.id = rep.employee_id) 
LEFT JOIN (SELECT count(ID) cnt, Employee_ID  
      FROM office_report 
      GROUP BY employee_ID) of_rep 
    ON (emp.id = of_Rep.employee_id) 
WHERE emp.user_id =7 AND emp.active = 1 
GROUP BY emp.id, emp.name 
ORDER BY emp.name ASC 

或使用相关查询（但不支持所有的时间，例如在创建物化从这个SQL视图）

SELECT emp.*, 
     (SELECT count(ID) 
     FROM REPORT 
     WHERE emp.id = rep.employee_id) Report_Cnt, 
     (SELECT count(ID) 
     FROM office_report of_REP 
     WHERE emp.id = of_Rep.employee_id) of_Rep_Cnt 
FROM employee emp 
WHERE emp.user_id =7 AND emp.active = 1 
GROUP BY emp.id, emp.name 
ORDER BY emp.name ASC