LeetCode刷题之工资最高的员工

工资最高的员工(Department Highest Salary)

题目

DepartmentHighestSalary

SQL脚本

  • MySQL

    
        # Write your MySQL query statement below
    
        --3. 关联部门表查最高员工所在部门名称
        SELECT 
            distinct
            d.name AS Department,
            em.name AS Employee,
            em.salary AS Salary
        FROM 
        --2. 关联员工表和最高薪资临时表查各部门最高工资员工信息
        (SELECT 
                e.name,
                e.salary,
                e.departmentId
        FROM employee e 
        INNER JOIN 
        --1. 查各个部门最高薪资
        (SELECT departmentId,MAX(salary) salary FROM employee GROUP BY departmentId) m
        ON e.salary = m.salary
        and e.departmentId = m.departmentId) em
        INNER JOIN 
        department d
        ON em.departmentId = d.id
    
    
  • Oracle

    
        select distinct
            d.name as Department,
            e.name as Employee,
            e.salary as salary
        from (
            select departmentid,id,name,salary,rank() over(partition by departmentid order by salary desc) as pm from employee
        ) e 
        innner join 
            department d 
        on 
            e.departmentid = d.id
        where e.pm = 1
            
    
    

官方解法

  • 思路分析

    思路分析

  • MySQL

    
        SELECT
            Department.name AS 'Department',
            Employee.name AS 'Employee',
            Salary
        FROM
            Employee
                JOIN
            Department ON Employee.DepartmentId = Department.Id
        WHERE
            (Employee.DepartmentId , Salary) IN
            (   SELECT
                    DepartmentId, MAX(Salary)
                FROM
                    Employee
                GROUP BY DepartmentId
            )
    
    
    
    
    
  • Oracle

    
    
        select b.Name as Department,
            t.Name as Employee,
            t.Salary as Salary
        from Department b
        left join
        (
        select Name,
            Salary,
            DepartmentId,
            rank() over(partition by DepartmentId order by Salary desc) as rank
        from Employee
        ) t
        on t.DepartmentId = b.Id
        where t.rank = 1;
    
    

其他

in关键字

  • 语法

        -- 匹配多个字段
        select id from table where (num,name) in ((num1,'name1'),(num2,'name2'))
    
    

Oracle常用分析函数

rank()

rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,比如:当第2名和第3名的利润相同时,rank的结果是1,2,2,4

dense_rank()

dense_rank则不会跳过这个排名,结果是1,2,2,3

row_number()

row_number哪怕是两个数据完全相同,排名也会不一样,结果是1,2,3,4
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值