如何编写SQL查询统计employee_test表中每位经理的下属员工数？

Got it, let's solve this problem properly. Since we need to count subordinates per manager and account for possible duplicate manager names, we have to make sure we're grouping by a unique identifier for each manager (their Id) instead of just their name. Here are two common scenarios you might need:

Scenario 1: Only include managers who are also employees in the table #

This query will list every employee who is a manager (even if they have no subordinates) and count their direct reports. Using the manager's Id ensures that even if two managers share the same name, their counts stay separate.

SELECT
    m.Id AS Manager_Id,
    m.Name AS Manager_Name,
    COUNT(e.Id) AS Subordinate_Count
FROM
    employee_test m
LEFT JOIN
    employee_test e ON m.Name = e.Manager_Name
GROUP BY
    m.Id, m.Name
ORDER BY
    m.Id;

Sample Output for Your Data:

Scenario 2: Include all managers (even those not listed as employees) #

If you need to count subordinates for managers who aren't in the employee table (like kishore in your sample data), we first get all distinct manager names from the table, then join back to count their subordinates:

SELECT
    COALESCE(m.Id, -1) AS Manager_Id, -- -1 marks managers not in the employee table
    mn.Manager_Name,
    COUNT(e.Id) AS Subordinate_Count
FROM
    (SELECT DISTINCT Manager_Name FROM employee_test) mn
LEFT JOIN
    employee_test m ON mn.Manager_Name = m.Name
LEFT JOIN
    employee_test e ON mn.Manager_Name = e.Manager_Name
GROUP BY
    mn.Manager_Name, COALESCE(m.Id, -1)
ORDER BY
    COALESCE(m.Id, -1);

Sample Output for Your Data:

The COALESCE here uses -1 as a placeholder for managers who aren't in the employee list (since they don't have an Id). This way, even if two non-employee managers share the same name, they'll be grouped together (since we don't have an Id to distinguish them), but that's unavoidable given the table structure.

内容的提问来源于stack exchange，提问作者Deepak S