如何获取拥有最高工资的员工姓名（关联employee与emp_salary表）

Let's walk through what's going wrong with your current query and how to fix it to get the results you want.

What's Wrong with Your Existing Query? #

Your current SQL:

select e.emp_name,MAX(es.salary) from employee e inner join emp_salary es on e.emp_id=es.emp_id group by es.salary

has two key issues:

• Grouping by es.salary means you're grouping rows by their salary value, not by employee. This doesn't align with your goal of finding employees tied to the highest salary.
• Depending on your SQL mode, this query might even throw an error because e.emp_name isn't included in the GROUP BY clause and isn't wrapped in an aggregate function (like MAX() or MIN()). Even if it runs, the results will be messy—you'll get random employee names paired with each salary group, not the ones with the maximum salary.

Solution 1: Subquery to Find the Highest Salary First #

The simplest approach is to first get the maximum salary value from emp_salary, then join the tables to find which employees have that salary:

SELECT e.empname
FROM employee e
INNER JOIN emp_salary es ON e.empid = es.empid
WHERE es.salary = (SELECT MAX(salary) FROM emp_salary);

This works because:

1. The subquery (SELECT MAX(salary) FROM emp_salary) grabs the highest salary in the table.
2. We then join employee and emp_salary and filter only rows where the salary matches that maximum value.
3. If multiple employees share the highest salary, all of their names will be returned (which is usually what you want).

Solution 2: Window Functions (For More Flexibility) #

If you need to handle edge cases (like ranking salaries or dealing with ties explicitly), window functions like RANK() or ROW_NUMBER() are great:

WITH ranked_employees AS (
    SELECT
        e.empname,
        es.salary,
        -- RANK() lets multiple employees share the #1 spot if they have the same salary
        RANK() OVER (ORDER BY es.salary DESC) AS salary_rank
    FROM employee e
    INNER JOIN emp_salary es ON e.empid = es.empid
)
SELECT empname
FROM ranked_employees
WHERE salary_rank = 1;

• Use RANK() if you want all employees with the highest salary to appear.
• Use ROW_NUMBER() instead if you only want one employee (even if there are ties)—it will assign a unique rank to each row, so only one will get salary_rank = 1.

Why This Works Better Than Your Original Query #

Both solutions focus on first identifying the highest salary value, then matching it back to the corresponding employees. Your original query tried to group by salary, which doesn't connect the maximum salary to the right employee records.

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