要求：撰寫一個 SQL 查詢，獲取 Employee 表中第 n 高的薪水（Salary）
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，n = 2 時，應回傳第二高的薪水 200，如果不存在第 n 高的薪水，那么查詢應回傳 null
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
分析：
1.首先需要求第n高的薪水，那么首先需要用到開窗函式dense_rank()進行一個排序
2.為了避免所求到的薪水重復，我們需要distinct去重
3.考慮到求取的結果要起一個別名"getNthHighestSalary(N)"，我最先考慮到的是使用字串拼接輸出結果，但是缺少對應的函式N，后來查閱了下資料發現這里我們可以使用CREATE FUNCTION 陳述句創建自定義函式，下面是CREATE FUNCTION陳述句用法：
CREATE FUNCATION <函式名> ( [ <引數1> <型別1> [ , <引數2> <型別2>] ] … )
RETURNS <型別>
BEGIN
RETURN (sql陳述句體);
END
SQL陳述句:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
select distinct a.sl from(select salary as sl,dense_rank() over(order by salary desc)as r
from employee)a where a.r=N
);
END
在mysql8.0以上版本創建自定義函式會遇到以下問題：
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
因為我們的bin-log日志沒有開啟
查看bin-log是否開啟
show variables like 'log_bin_trust_function_creators';
解決方案：
1.我們需要在資料庫中輸入以下內容，
set global log_bin_trust_function_creators=1;
2.使用1的方法在計算機重啟后失效，為了永久有效，需要在在my.cnf組態檔中添加：
log_bin_trust_function_creators=1
運行：
drop FUNCTION if exists getNthHighestSalary;
DELIMITER //
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
select distinct a.sl from(select salary as sl,dense_rank() over(order by salary desc)as r
from employee)a where a.r=N
);
END//
DELIMITER ;
SELECT getNthHighestSalary(2);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/282165.html
標籤：其他