(一)题目
(二)参考答案:
1.比较简单易懂的方法:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE
M INT;
SET
M = N -1; RETURN(
#Write your MySQL query statement below.
SELECT DISTINCT
Salary
FROM
Employee
ORDER BY
Salary
DESC
LIMIT M,
1
);
END
答案分析:
薪水去重,按照薪水倒序排序,最大的放在第一位,选择第二大的,可以用distinct去重也能用group by分组达到去重效果,代码如下
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1;
RETURN (
# Write your MySQL query statement below.
SELECT
salary
FROM
employee
GROUP BY
salary
ORDER BY
salary DESC
LIMIT N, 1
);
END
2.子查询
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT e.salary
FROM
employee e
WHERE
N-1 = (SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary)
);
END
答案分析:
一般在子查询中,程序先运行在嵌套在最内层的语句,再运行外层。因此在写子查询语句时,可以先测试下内层的子查询语句是否输出了想要的内容,再一层层往外测试,增加子查询正确率。否则多层的嵌套使语句可读性很低。
薪水去重,子查询查出比e.salary大的个数,若满足n-1,输出e.salary
3.连麦表查询
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
e1.salary
FROM
employee e1 JOIN employee e2 ON e1.salary <= e2.salary
GROUP BY
e1.salary
HAVING
count(DISTINCT e2.salary) = N
);
END
答案分析:
连接条件设定为表1的salary小于表2的salary,group by去重,进行个数判断是否等于N
知识点补充:limit 函数
- 一个参数时代表查询数量
- 两个参数时,第一个参数代表检索偏移量(从0开始),第二个参数代表查询数量
//查询按学号排序后学生表中的前四项数据(即第1-4行)
select * from Student order by SNO limit 4;
//查询按学号排序后学生表中的从第四项到最后一项的数据中的前两项(即排序表中的第4、5两行)
select * from Student order by SNO limit 4,2;
此处有参考https://blog.csdn.net/a912952381/article/details/80732104