• 【LeetCode刷题】SQL-Second Highest Salary 及扩展以及Oracle中的用法


    转载于:https://www.cnblogs.com/contixue/p/7057025.html

    Write a SQL query to get the second highest salary from the Employee table.

    +----+--------+
    | Id | Salary |
    +----+--------+
    | 1  | 100    |
    | 2  | 200    |
    | 3  | 300    |
    +----+--------+
    

    For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null

    +---------------------+
    | SecondHighestSalary |
    +---------------------+
    | 200                 |
    +---------------------+
    

    Subscribe to see which companies asked this question.

    翻译:

    题目的意思是,在Employee表中找到第二大的Salary字段,然后以别名“SecondHighestSalary ”的形式输出

    解题思路:

    本题有两种思路。

    一种是找到找到最大值,然后我们取比最大值小一点的最大值。

    SQL:

    SELECT Max(Salary) as SecondHighestSalary FROM Employee WHERE Salary< ( SELECT  Max(Salary) FROM Employee);

     Max()函数表示返回最大值,如果没有最大值则返回NULL

    Salary<( SELECT Max(Salary) FROM Employee) 则是子查询的概念。

    例如 Select * From Table_A Where key in (a,b);  如果其中(a,b)的数据来源于Table_B或者Table_A表。我们就可以使用子查询,将(a,b)的数据替换为我们查询到的数据。

    Select * From Table_A Where key in (Select a,b From Table_B  Where Inr='XXXXXXX');

    “as XXX”则表示将输出的数据以“XXX”为别名的方式输出。

     

    还有一种方法就是,我们将数据进行排序,然后取第二位数。

    这种方法的难点在于:1、数据可能会有重复项。

              2、需要先排序再取数,所以不能用between 方法因为between方法必须要在order by 之前。

              3、整个表都是同一个数据的极端情况。

    对于情况3容易产生的错误写法:

    select Salary SecondHighestSalary from employee 
    order by salary limit 1,1

      要解决以上3个难点。我们首先使用DISTINCT方法将数据的重复项过滤只剩一条,然后使用LIMIT分页的方法取第二个值,对于情况3中的极端情况,我们使用 IFNULL来进行判断,如果没取到值就返回NULL。

      需要注意的是,LIMIT是MySQL中的用法,Oracle是不支持LIMIT的。

    SQL:

    SELECT IFNULL( (SELECT distinct Salary  FROM Employee order by Salary desc limit 1,1),null)as SecondHighestSalary;

    其中LIMIT 的用法是这样的:

    LIMIT m,n      "m"表示偏移量,表示从第几位开始取。"n"表示长度,即需要取多少位。

      需要注意的是偏移量的第一位为0,所以"LIMIT 1,1"表示从第二位开始取长度为1的数也就是第二位数。

      "LIMIT 0,n"或者"LIMIT n "则表示从第一位开始取,一直取到第n位为止。"LIMIT m,-1"表示从第m位开始取,一直取到表的末尾。

      distinct 表示将查询到的结果过滤重复项,只保留一项。

    IFNULL(Exp1,Exp2),类似一个三元表达式,如果Exp1不为空则返回Exp1的结果集,如果为空就返回Exp2.

    方法二的关键点在于排序和取数,排序我们除了使用ORDER BY 方法之外,还可以使用比较的方式排序。

    SQL:

    SELECT DISTINCT(e2.Salary) FROM Employee e1,Employee e2 WHERE e2.Salary>=e1.Salary;

    使用“>=”的方式,是为了避免Salary字段有0值而导致无法比较

    sql空值表示:

    在允许空值的情况下,空值是NULL(null在表中为字符串类型),指没有填写过数据;
    在不允许为空的情况下,数值型字段默认为0)

    需要注意的是,这种排序方式非常非常的消耗系统资源,在实际生产中使用这种方式来排序是不负责任的行为(见https://www.cnblogs.com/MarsDing/p/9856272.html),所以如非必要尽量不要使用这种方式而使用更快捷消耗更小的的Order by。

    现在已经排好序了,那么接下来我们要进行取数。取数可以使用上面的LIMIT方法。

    LIMIT方法:

    SELECT IFNULL((SELECT DISTINCT(e2.Salary) FROM Employee e1,Employee e2 WHERE e2.Salary>e1.Salary LIMIT 1,1),NULL) as SecondHighestSalary;

    即,将:order by Salary desc 替换为 WHERE e2.Salary>e1.Salary 

    除此之外还有其他方法吗?我们知道,SELECT语句实际上是个循环语句,既然是个循环语句而且已经排好序,那么也就是说如果我们要取第二大的数据,我们可以取当他循环到第二次时的那一行。怎么取到那一行呢。学过For循环都知道,要先对这个循环进行计数,然后循环到我们需要的那一次再取值。那么SQL中怎么计数呢。答案是使用COUNT方法。

    COUNT方法

    SELECT e1.Salary FROM Employee e1 WHERE 2=(Select Count(distinct(e2.Salary)) from Employee e2 where e2.Salary >= e1.Salary)  as SecondHighestSalary;

    上面两种方法也可以用于,取任意第N位数据时。

    COUNT方法之所以不能使用Order by,是因为Order by是对结果进行排序,而我们需要的是已排序好的结果。

     如果要使用Order by 方法,需要再加一层子查询。

    COUNT-Order By 方法

    SELECT Salary FROM Employee  WHERE 2=(Select Count(distinct(Salary)) from (Select Salary From Employee order by Salary desc))  as SecondHighestSalary;

    .

    由于Oracle中不能使用LIMIT,所以我们用rownum来处理此问题

    Select Salary from (select amt ,rownum n From (Select distinct amt from Employee order by Salary desc)) where n=2;

    其中的n=2可以替换为任意正整数,即为第N位。

    总结:对一个题可以通过不同角度思考并优化解答,这需要长期不断地积累各种知识点并汇总结合

  • 相关阅读:
    (免量产,免格式化)手动将PE安装到移动硬盘/U盘或无系统硬盘!
    OFFICE文档(DOC,XLS,PPT)打开报错的解决办法!
    <JavaScript><DOM> 十. 核心DOM的属性
    <JavaScript> 九. BOM其它对象(screen对象, navigator对象, location地址栏对象, history历史记录对象)
    <JavaScript> 八. 定时器
    <JavaScript> 七. 延时器
    <JavaScript> 六. window对象的属性和方法
    <JavaScript> 五. Number对象的属性和方法
    <JavaScript> 四. Math对象的属性和方法
    <JavaScript> 三. Date对象的属性和方法
  • 原文地址:https://www.cnblogs.com/MarsDing/p/9870715.html
Copyright © 2020-2023  润新知