第二高的薪水 & 第N高的薪水

    技术2022-07-21  96

    问题一:

    编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。

    Employee 表

    IdSalary110022003300

    例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。

    查询结果:

    SecondHighestSalary200

    来源:力扣(LeetCode) 链接:https://leetcode-cn.com/problems/second-highest-salary

    问题一解法:

    SELECT ( SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1, 1 ) AS SecondHighestSalary

    为什么要使用DISTINCT呢?

    当薪水一样时,不应该获取到薪水,那么,就需要现在同样薪水的数据合成一条数据来处理。这里再补充一下

    mySql的执行顺序:

    mysql执行sql的顺序从 From 开始,以下是执行的顺序流程

    FROM table1 left join table2 on 将table1和table2中的数据产生笛卡尔积,生成Temp1

    JOIN table2 所以先是确定表,再确定关联条件

    ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2

    WHERE 对中间表Temp2产生的结果进行过滤 产生中间表Temp3

    GROUP BY 对中间表Temp3进行分组,产生中间表Temp4

    HAVING 对分组后的记录进行聚合 产生中间表Temp5

    SELECT 对中间表Temp5进行列筛选,产生中间表 Temp6

    DISTINCT 对中间表 Temp6进行去重,产生中间表 Temp7

    ORDER BY 对Temp7中的数据进行排序,产生中间表Temp8

    LIMIT 对中间表Temp8进行分页,产生中间表Temp9

    因为DISTINCT在ORDER BY和LIMIT之前执行,所以在mysql进行排序和获取条数操作时,同样的薪水已经被合成为一条,再被操作的。

    问题二:

    编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。

    Employee 表

    IdSalary110022003300

    例如上述 Employee 表,n = 2 时,应返回第二高的薪水 200。如果不存在第 n 高的薪水,那么查询应返回 null。

    查询结果:

    getNthHighestSalary(2)200

    来源:力扣(LeetCode) 链接:https://leetcode-cn.com/problems/nth-highest-salary

    问题二解法:

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN SET N := N-1; RETURN ( # Write your MySQL query statement below. select * from (SELECT distinct salary FROM employee ORDER BY salary DESC LIMIT N, 1 ) as `getNthHighestSalary(N)` ); END

    这里需要使用到mysql的函数来进行处理,其中,N是函数的参数

    由于LIMIT是从0开始的,我们想要第二高薪水时,应该是LIMIT 1,0,即LIMIT (N-1),0。但是,mysql并不支持写N-1,所以需要先将N变量-1

    SET N := N-1;

    distinct用法参照问题一解法的说明

    这里取别名为getNthHighestSalary(N),N并不是变量,不会跟着更改,但是成功过关了,这是为什么?因为sql语句中是这样调用函数:

    select getNthHighestSalary(2);

    返回的结果字段名,便是直接使用了上面select后面的函数作为字段名了

    也就是说,上面函数最后面的 as 'getNthHighestSalary(N)'其实完全没卵用(别打别打,顺手嘛,给大家分享分享)

    创建自定义函数的方法说明

    DELIMITER $$ DROP FUNCTION IF EXISTS genPerson$$ CREATE FUNCTION genPerson(name varchar(20)) RETURNS varchar(50) BEGIN DECLARE str VARCHAR(50) DEFAULT ''; SET @tableName=name; SET str=CONCAT('create table ', @tableName,'(id int, name varchar(20));'); return str; END $$ DELIMITER ; DELIMITER $$ 定义结束符。MySQL默认的结束符是分号,但是函数体中可能用到分号。为了避免冲突,需要另外定义结束符。DROP FUNCTION IF EXISTS genPerson$$ 如果函数genPerson已经存在了,就删除掉。CREATE FUNCTION 创建函数genPerson,函数的参数是name,返回值是varchar(50)。函数体放在BEGIN 与 END之间。DECLARE 声明变量,str类型是varchar(50),默认值是空。CONCAT连接多个字符串。RETURN 返回拼接后的字符串str。

    最后哔哔一句:如果是实际应用的话,直接按照问题一的方式,然后结合PHP,将N设为变量嵌入sql语句更方便……

    Processed: 0.015, SQL: 9