SQL Server中查询员工信息并统计对应职位总员工数的正确SQL语句咨询

你的问题出在GROUP BY子句的范围上：你把E.HireDate、P.FirstName、P.LastName这些每个员工唯一的字段都加入了分组条件，这会导致每个分组只包含单个员工，所以COUNT(E.JobTitle)自然只会返回1。要实现“每行员工信息附带所属职位总人数”的需求，你可以用以下两种方法：

方法一：使用窗口函数（推荐，简洁高效） #

窗口函数可以在不改变原有结果集行数的前提下，对指定分组进行统计计算。这里我们用COUNT(*) OVER (PARTITION BY E.JobTitle)来计算每个职位的总人数：

SELECT 
    E.JobTitle,
    E.HireDate,
    P.FirstName,
    P.LastName,
    COUNT(*) OVER (PARTITION BY E.JobTitle) AS JobTitleTotalEmployees
FROM AdventureWorks2019.HumanResources.Employee E
JOIN AdventureWorks2019.Person.Person P ON E.BusinessEntityID = P.BusinessEntityID;

原理说明 #

• PARTITION BY E.JobTitle：将整个结果集按职位进行分区
• COUNT(*) OVER (...)：计算每个分区内的总记录数，也就是对应职位的员工总数，并把这个值附加到该分区的每一行数据中

方法二：使用子查询/CTE预统计职位人数 #

如果你更习惯用传统的分组统计+关联的方式，可以先预统计每个职位的总人数，再和员工表、人员表关联：

WITH JobCounts AS (
    -- 先统计每个职位的总员工数
    SELECT JobTitle, COUNT(*) AS JobTitleTotalEmployees
    FROM AdventureWorks2019.HumanResources.Employee
    GROUP BY JobTitle
)
SELECT 
    E.JobTitle,
    E.HireDate,
    P.FirstName,
    P.LastName,
    JC.JobTitleTotalEmployees
FROM AdventureWorks2019.HumanResources.Employee E
JOIN AdventureWorks2019.Person.Person P ON E.BusinessEntityID = P.BusinessEntityID
JOIN JobCounts JC ON E.JobTitle = JC.JobTitle;

原理说明 #

1. 用CTEJobCounts先单独统计每个职位的员工总数
2. 通过JOIN将这个统计结果与原员工、人员表关联，让每个员工都能匹配到对应职位的总人数

两种方法都能满足你的需求，其中窗口函数的写法更简洁，执行效率也通常更高，推荐优先使用。

内容的提问来源于stack exchange，提问作者Lihi Eldor