基于职位角色查询薪资最高与最低员工姓名的SQL需求及表结构

Let's start by fixing the issues in your original query and building a solution that meets your requirement:

What Was Wrong With the Original Query? #

Your original SQL has two critical problems:

1. The subquery inside IN returns two columns (EMPLOYEEID, JOBROLES), but IN only accepts a single column of values.
2. Grouping by JOBROLES without using aggregate functions on EMPLOYEEID is invalid in standard SQL (it won't reliably return meaningful employee IDs), and it doesn't filter for maximum or minimum salaries at all.

The Correct Solution #

This approach uses Common Table Expressions (CTEs) to break down the problem into readable steps, and handles cases where multiple employees might share the same highest/lowest salary in a role:

WITH EmployeeTotalSalaries AS (
    -- Calculate total salary (base + allowances) for each employee
    SELECT 
        s.EMPLOYEEID,
        s.JOBROLES,
        (s.BASICSAL + s.ALLOWANCES) AS TOTAL_SALARY
    FROM Salary25 s
),
JobSalaryBounds AS (
    -- Find the max and min total salary for each job role
    SELECT 
        JOBROLES,
        MAX(TOTAL_SALARY) AS MAX_SALARY,
        MIN(TOTAL_SALARY) AS MIN_SALARY
    FROM EmployeeTotalSalaries
    GROUP BY JOBROLES
)
-- Join all data to get employee names along with their salary type (highest/lowest)
SELECT 
    e.FIRSTNAME,
    e.LASTNAME,
    ets.JOBROLES,
    ets.TOTAL_SALARY,
    CASE 
        WHEN ets.TOTAL_SALARY = jsb.MAX_SALARY THEN 'Highest Salary'
        WHEN ets.TOTAL_SALARY = jsb.MIN_SALARY THEN 'Lowest Salary'
    END AS SALARY_CATEGORY
FROM Employee24 e
INNER JOIN EmployeeTotalSalaries ets 
    ON e.EMPLOYEEID = ets.EMPLOYEEID
INNER JOIN JobSalaryBounds jsb 
    ON ets.JOBROLES = jsb.JOBROLES
WHERE ets.TOTAL_SALARY IN (jsb.MAX_SALARY, jsb.MIN_SALARY)
ORDER BY ets.JOBROLES, SALARY_CATEGORY;

How This Works #

1. EmployeeTotalSalaries: Computes the total compensation for each employee by adding their base salary and allowances.
2. JobSalaryBounds: Aggregates the total salaries to find the highest and lowest values for each job role.
3. Main Query: Joins the employee details with their total salaries and the role-specific salary bounds. It filters to only include employees whose total salary matches either the max or min for their role, and adds a clear label to indicate which category they fall into.

Notes #

• If multiple employees in the same role have the same highest or lowest salary, all of them will be included in the results.
• If a role has only one employee, they will appear in both the "Highest Salary" and "Lowest Salary" categories (you can adjust the CASE statement if you want to avoid this, but it's accurate to their role's salary range).

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