从一个表中进行选择，从另外两个表中进行计数 [英] Select from one table with count from two other tables

问题描述

我正在做一个查询，其中我从一个名为employee的表中选择全部，并希望从另外两个表中对employee_id进行计数，并在2个单独的列中表示该计数.

I'm doing a query where i select all from a table called employee and want to do a count of employee_id from two other tables and represent the count in 2 seperate columns.

表格:

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

问题是，一旦我在两个报表表中都有报表，计数就混乱了.假设我在报告表中有16个报告，在 office_report 表中有2个报告，则 no_of_field_reports 和 no_of_office_reports 的计数将变为32.

The problem is, as soon as i have reports in BOTH report tables the count messes up. Say i have 16 reports in report table and 2 in office_report table, the count for no_of_field_reports and no_of_office_reports will become 32.

我显然遗漏了一些东西，但是由于我不是SQL天才，所以我不知道是什么.

Im missing something obviously but as I'm not a SQL genius I can't figure out what.

请确保解释引起问题的原因，以便我可以从错误中学习并更好地理解这些类型的查询，因为这将不是最后一次了.

Please make sure to explain what is causing the problem so I'm able to learn from my mistakes and get a better understanding of these type of queries as this is not going to be the last time.

我想答案通常与mariaDB，mySQL和SQL相同，因此为了引起注意，我添加了所有这些标签.

I guess the answer will be the same for mariaDB, mySQL, and SQL in general so i added all those tag's for the sake of attention..

推荐答案

如果您希望获得不同的计数，则可能是一种方法(尽管您可能需要调整到PK字段)

Possibly one approach if you're after distinct counts ( though you may need to adjust to the PK field)

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

如果您不在连接计数之前，那么在连接之前获取计数的方法很可能是正确的方法，并且提供了灵活性.

An approach getting the counts before the joins if you're not after a distinct count then this is likely the right approach and offers flexibility.

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创建实例化视图时)

or use of correlated queries (but not supported all the time Such as when creating materialized views from this 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

这篇关于从一个表中进行选择，从另外两个表中进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！