Skip to content
Branch: master
Find file History
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
..
Failed to load latest commit information.
README.md
functions.sql
solve.sql

README.md

Nth Highest Salary

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

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

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

Solution

SecondHighestSalary

mysql 函数

mysql 可以自定义过程(无返回值)和函数(有返回值),参数也可以定义为IN或者OUT,IN就是传人的参数,OUT就是要写进去的参数(有点像传引用)

语法:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

定义例子:

delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
 BEGIN
	SELECT COUNT(*) INTO param1 FROM t;
END//

使用call simpleproc(@count)调用以上过程,然后select @count输出结果

定义函数例子:

delimiter $$
drop function if exists getNthHighestSalary $$
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
	declare x int;
	set N = N - 1;
	set x = (select distinct Salary from Employee order by Salary desc limit N, 1);
	if isnull(x)
		then
		return null;
	else
		return x;
	end if;
END$$
delimiter ;

调用select hello('world');输出Hello, world!

mysql变量

mysql使用declare 变量名 变量类型声明变量, 使用set 变量名 = 值设置变量值

mysql分支循环

if语句
if(condition) then s1;
elseif (condition) then s2;
else s3;
end if;

[begin_label:]while 循环
while condition do
s1;
s2;
end while[end_label]

Code

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
	declare x int;
	set N = N - 1;
	set x = (select distinct Salary from Employee order by Salary desc limit N, 1);
	if isnull(x)
		then
		return null;
	else
		return x;
	end if;
END
You can’t perform that action at this time.