我想下面的SQL会起作用。

select Iname, dept_name from instructor as t1 where t1.salary=(select max(salary) from instructor as t2 where t1.dept_name = t2.dept_name);

字符串
或

select t1.Iname, t1.dept_name from instructor as t1 left join instructor as t2 on t1.dept_name = t2.dept_name and t1.salary < t2.salary where t2.dept_name is NULL;

型

在MySQL 8+上，这个问题很容易使用RANK处理：

WITH cte AS (
    SELECT *, RANK() OVER (PARTITION BY dept_name ORDER BY salary DESC) rnk
    FROM department
)

SELECT Iname, dept_name, salary
FROM cte
WHERE rnk = 1;

字符串
请注意，如果出现平局，上述报告将报告给定部门内共享最高工资的两个或更多讲师。

使用EXISTS

select T.Iname, 
        T.dept_name 
 from instructor as T
 where not exists (select 1
                   from instructor as S
                   where S.dept_name = T.dept_name
                   and T.salary < S.salary);

字符串

另一种方法是在子查询td_1中查找部门的最大工资，然后使用salary和department将其与数据表连接

select td.Iname, td.dept_name, td.salary
  from test_data td
  join (select max(salary) salary, dept_name
          from test_data td 
         where td.dept_name = dept_name
         group by dept_name) td_1
    on td_1.dept_name = td.dept_name
   and td_1.salary = td.salary

字符串

试试这个：

SELECT ID, Iname, dept_name, salary 
    FROM instructors
GROUP BY Iname,dept_name 
ORDER BY salary DESC ;

字符串