如何在SQL关联查询结果中新增部门平均薪资列（基于Adventure Works数据库）

Hey there! Great job getting that correlated subquery working for identifying employees earning above their department average—those can definitely be tricky to wrap your head around at first, and nailing it 9 out of 10 times is a solid win. Let's tackle adding that department average salary column to your results.

There are two clean ways to approach this, depending on whether you want to stick with subquery-style logic or leverage a more efficient window function:

Option 1: Use a Window Function (Most Efficient & Clean) #

Window functions let you calculate aggregate values (like average salary) across a subset of rows (your department) while keeping all individual employee rows in your output. Here's how to modify your query:

SELECT 
  CONCAT(v_dep.FirstName, ' ', v_dep.LastName) AS Full_name,
  eph.Rate AS Employee_Salary,
  AVG(eph.Rate) OVER (PARTITION BY v_dep.Department) AS Department_Average_Salary
FROM HumanResources.vEmployeeDepartment v_dep
JOIN HumanResources.EmployeePayHistory eph 
  ON v_dep.BusinessEntityID = eph.BusinessEntityID
WHERE eph.Rate > (
  SELECT AVG(eph2.rate) 
  FROM HumanResources.vEmployeeDepartment v_dep2
  JOIN HumanResources.EmployeePayHistory eph2 
    ON v_dep2.BusinessEntityID = eph2.BusinessEntityID
  WHERE v_dep2.Department = v_dep.Department
);

How this works: #

• AVG(eph.Rate) OVER (PARTITION BY v_dep.Department) calculates the average salary for every row's department, and attaches that value to each employee in the department.
• You keep your original WHERE clause to filter only employees earning above the department average.

Option 2: Precompute Department Averages with a CTE (More Intuitive) #

If window functions still feel a bit abstract, you can first calculate each department's average salary in a Common Table Expression (CTE), then join it back to your main query. This makes the department average calculation explicit:

WITH DepartmentSalaries AS (
  SELECT 
    v_dep2.Department,
    AVG(eph2.rate) AS Department_Average_Salary
  FROM HumanResources.vEmployeeDepartment v_dep2
  JOIN HumanResources.EmployeePayHistory eph2 
    ON v_dep2.BusinessEntityID = eph2.BusinessEntityID
  GROUP BY v_dep2.Department
)
SELECT 
  CONCAT(v_dep.FirstName, ' ', v_dep.LastName) AS Full_name,
  eph.Rate AS Employee_Salary,
  ds.Department_Average_Salary
FROM HumanResources.vEmployeeDepartment v_dep
JOIN HumanResources.EmployeePayHistory eph 
  ON v_dep.BusinessEntityID = eph.BusinessEntityID
JOIN DepartmentSalaries ds 
  ON v_dep.Department = ds.Department
WHERE eph.Rate > ds.Department_Average_Salary;

How this works: #

• The DepartmentSalaries CTE runs first, grouping employees by department and calculating each department's average salary.
• We then join this CTE to our main employee data on the department name, so we can pull in the average salary for each employee's department.
• The WHERE clause now compares the employee's salary directly to the precomputed department average from the CTE.

Quick Tip: #

If you're still struggling with correlated subquery terminology, think of them as "row-specific" subqueries—each row in your main query triggers the subquery to calculate a value just for that row's department. Window functions eliminate this repeated calculation, which can make your query faster for large datasets.

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