从一个表中选择，并从另外两个表中进行计数

2022-01-15 00:00:00 sql mariadb mysql

我正在做一个查询，我从一个名为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. 员工[身份证等]
  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

问题是，一旦我在两个报告表中都有报告，计数就会混乱.假设我在 report table 中有 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

相关文章