欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

LeetCode——Department Highest Salary(花式使用IN以及GROUP BY)

程序员文章站 2023-11-09 21:19:52
以前使用 ,都是局限于单个数值使用,从未尝试过多个数据使用 . 此题涉及两个表,肯定需要使用 操作. 此外,需要选取每个 的最大数值,那么肯定涉及 以及 操作. 综合以上因素,答案如下所示: ......
the employee table holds all employees. every employee has an id, a salary, and there is also a column for the department id.

+----+-------+--------+--------------+
| id | name  | salary | departmentid |
+----+-------+--------+--------------+
| 1  | joe   | 70000  | 1            |
| 2  | jim   | 90000  | 1            |
| 3  | henry | 80000  | 2            |
| 4  | sam   | 60000  | 2            |
| 5  | max   | 90000  | 1            |
+----+-------+--------+--------------+
the department table holds all departments of the company.

+----+----------+
| id | name     |
+----+----------+
| 1  | it       |
| 2  | sales    |
+----+----------+
write a sql query to find employees who have the highest salary in each of the departments. for the above tables, your sql query should return the following rows (order of rows does not matter).

+------------+----------+--------+
| department | employee | salary |
+------------+----------+--------+
| it         | max      | 90000  |
| it         | jim      | 90000  |
| sales      | henry    | 80000  |
+------------+----------+--------+

以前使用in,都是局限于单个数值使用,从未尝试过多个数据使用in.
此题涉及两个表,肯定需要使用join操作.
此外,需要选取每个department 的最大数值,那么肯定涉及max以及group by操作.
综合以上因素,答案如下所示:

# write your mysql query statement below
select employee.name as employee, employee.salary, department.name as department 
from employee, department 
where 
    employee.departmentid = department.id
    and (employee.departmentid, employee.salary)
    in 
        (select employee.departmentid, max(employee.salary)
         from employee
         group by employee.departmentid);