在第二表的两列中获取一个表中出现的值的计数

Question

我非常希望在找到下面所述场景中最有效的MySQL查询方面提供一些帮助。我添加了一个小的数据片段作为示例，但是查询的数据将是巨大的，并且只会随着时间的推移而膨胀，因此我非常希望找到最有效的方法来获得预期的结果。谢谢你的阅读，我希望有人能为我澄清这一点。

表名雇员

+-------------+---------------+
| employee_id | employee_name |
+-------------+---------------+
|     0000001 | James         |
|     0000002 | David         |
|     0000003 | Mark          |
|     0000004 | Gemma         |
|     0000005 | Hayley        |
|     0000006 | Sunil         |
+-------------+---------------+ 

表名employees_orders

+----------+---------------------+-------------------+
| order_id | Lead_generated_by   | sale_generated_by |
+----------+---------------------+-------------------+
|    00001 | James               | David             |
|    00002 | Mark                | Gemma             |
|    00003 | Hayley              | David             |
|    00004 | James               | Mark              |
|    00005 | Gemma               | Hayley            |
|    00006 | Sunil               | Mark              |
|    00007 | David               | James             |
|    00008 | Hayley              | Sunil             |
|    00009 | Gemma               | James             |
|    00010 | Hayley              | David             |
+----------+---------------------+-------------------+

预期结果

+---------------+-----------------+-----------------+
| employee_name | leads_generated | sales_generated |
+---------------+-----------------+-----------------+
| James         |               2 |               2 |
| David         |               1 |               3 |
| Mark          |               1 |               2 |
| Gemma         |               2 |               1 |
| Hayley        |               3 |               1 |
| Sunil         |               1 |               1 |
+---------------+-----------------+-----------------+

Answer 1

与此类似的东西应该能发挥作用：

SELECT
        e.employee_name,
        coalesce(l.count,0) AS leads_generated,
        coalesce(s.count,0) AS sales_generated
    FROM
        employees e
        LEFT JOIN ( SELECT Lead_generated_by as employee_name, count(*) AS count FROM employees_orders GROUP BY 1 ) l ON ( e.employee_name = l.employee_name )
        LEFT JOIN ( SELECT sale_generated_by as employee_name, count(*) AS count FROM employees_orders GROUP BY 1 ) s ON ( e.employee_name = s.employee_name )
    GROUP BY 1
    ORDER BY 1 ASC;

尽管如此，还是有几点建议：

performance.

• Take

• 考虑使用employee_id代替基于字符的_generated_by字段，以更好地执行步骤，以确保在employees中列出所有员工，employee_id可以帮助使用这些步骤(例如，使用外键知道，上面的查询假定employee_name中没有不在employees.中的名称)。