如何在SQL查询中选择所有列并添加部门员工总数

我需要查询表的所有列，同时在每行添加对应部门的员工总数。以下是创建示例数据的代码：

DECLARE @CompanyTable TABLE
     (
    [EMPID] int NOT NULL,
    [FNAME] [nvarchar](60) NOT NULL,
    [LNAME] [nvarchar](60) NOT NULL,
    [COUNTRY] [varchar](50) NOT NULL,
    [CITY] [nvarchar](50) NOT NULL,
    [DEPARTMENT] [nvarchar](30) NOT NULL)
insert into @CompanyTable VALUES (1,  'ANGEL', 'HILL','US','SF','HR')
,(2,  'ANGEL', 'HILL','US','TAMPA','HR')
,(3,  'VICTOR', 'FENCL','US','NEW YORK','TECH')
,(4,  'ANNA', 'NEAL','US','CLEVELAND','HR')
,(5,  'CARRIE', 'NICHOLS','CANADA','EDMINTON','HR')
,(6,  'CATHY', 'HILL','CANADA','TORONTO','PAYROLL')
,(7,  'LINDA', 'REED','CANADA','TORONTO','HR')
,(8,  'ANNA', 'NEAL','UK','LONDON','HR')
,(9,  'JAIME', 'RODRIGUEZ','UK','LONDON','TECH')
,(10,  'DANA', 'NICHOLS','US','SF','HR')
,(11,  'ANGEL', 'WEBB','US','SF','PAYROLL')
,(12,  'CATHY', 'JORDAN','US','SF','TECH');

我已经能通过以下分组查询得到各部门员工数：

SELECT 
     [DEPARTMENT],  count([EMPID]) [EMP_COUNT]
  FROM @CompanyTable
  GROUP by [DEPARTMENT]

但我希望实现查询所有列，并在每行显示所属部门的员工总数，期望结果如下：

EMPID   FNAME   LNAME     COUNTRY CITY        DEPARTMENT    EMP_COUNT
1       ANGEL   HILL      US      SF          HR              7
2       ANGEL   HILL      US      TAMPA       HR              7
3       VICTOR  FENCL     US      NEW YORK    TECH            3
4       ANNA    NEAL      US      CLEVELAND   HR              7
5       CARRIE  NICHOLS   CANADA  EDMINTON    HR              7
6       CATHY   HILL      CANADA  TORONTO     PAYROLL         2
7       LINDA   REED      CANADA  TORONTO     HR              7
8       ANNA    NEAL      UK      LONDON      HR              7
9       JAIME   RODRIGUEZ UK      LONDON      TECH            3
10      DANA    NICHOLS   US      SF          HR              7
11      ANGEL   WEBB      US      SF          PAYROLL         2
12      CATHY   JORDAN    US      SF          TECH            3

最佳实现方式 #

使用窗口函数是最简洁高效的方案，不需要额外的子查询或关联操作，直接在原表查询的基础上计算部门员工总数：

SELECT 
    *,
    COUNT([EMPID]) OVER(PARTITION BY [DEPARTMENT]) AS [EMP_COUNT]
FROM @CompanyTable

说明：

• COUNT([EMPID]) OVER(PARTITION BY [DEPARTMENT])：通过PARTITION BY按部门分组，对每个分组内的员工ID计数，将结果附加到每行数据中。
• 这种方式避免了分组后再关联回原表的开销，执行效率更高，代码也更简洁。

执行上述语句后，就能得到你期望的结果——每行显示员工的所有信息，同时带上所属部门的员工总数。

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