使用Count with Count Distinct和Group By

Question

我有两个表，一个是employees的，它有id，name和company列，另一个是调查results的表，它有employee_id，questions_id，answer作为一个员工对多个结果。

|---------------------|
| id | name | company |
|---------------------|

|-----------------------------------------|
| id | employee_id | question_id | answer |
|-----------------------------------------|

我想要Select每个公司的员工总数，以及每个公司在调查中的总参与者。

我尝试了以下查询，但执行时间太长：

SELECT employees.company as x, COUNT(DISTINCT (results.employee_id)) "Total Surveys", (SELECT COUNT(employees.id) FROM employees WHERE company = x) "Headcount"
FROM results
JOIN employees ON results.employee_id = employees.id
GROUP BY employees.company

结果

|--------------------------------|
|  x | Total Surveys | Headcount |
|--------------------------------|
| C1 |       15      |    3      |
| C2 |       10      |    5      |
|--------------------------------|

SQL Fiddle

有什么建议吗？

Answer 1

您可以通过从employees到results的LEFT JOIN获得您想要的结果；然后，您可以计算这两个值，而无需使用子查询：

SELECT e.company, 
       COUNT(DISTINCT r.employee_id) AS `Total Surveys`,
       COUNT(DISTINCT e.id) AS `HeadCount`
FROM employees e
LEFT JOIN results r ON r.employee_id = e.id
GROUP BY e.company

Demo on SQLFiddle