Can you solve this real interview question? Nth Highest Salary - Table: Employee +---+---+ | Column Name | Type | +---+---+ | id | int | | salary | int | +---+---+ id is the primary key (column wit
因此,对代码改动如下所示: CREATEFUNCTIONgetNthHighestSalary(NINT)RETURNSINTBEGINDECLAREPINTDEFAULTN-1; IF (P<0)THENRETURNNULL;ELSERETURN( # Write your MySQL query statement below.SELECTIFNULL( (SELECTDISTINCTSalaryFROMEmployeeORDERBYSalaryDESCLIMIT P,1) ,NULL)ASSecondHighestSalary );ENDIF;END PS: ...
CREATEFUNCTIONgetNthHighestSalary(NINT)RETURNSINT BEGIN declareMint; setM=N-1; RETURN( # Write your MySQL query statement below. selectdistinctSalaryfromEmployeeorderbySalarydesclimit N-1, 1 ); END
Leetcode数据库题解-177. Nth Highest Salary 题目地址:https://leetcode.com/problems/nth-highest-salary/ 题解:这道题是之前那道Second Highest Salary的拓展,根据之前那道题的做法,我们可以很容易的将其推展为N,根据对Second Highest Salary中解法一的分析,我们只需要将OFFSET后面的1改为N-1就行了,但是...
SELECT MAX(CASE WHEN rnk = 2 THEN Salary ELSE null END) AS SecondHighestSalary FROM ( SELECT Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) AS rnk FROM Employee ) AS tmp 将rnk = 2 可以改成任意值。由于本道 Leetcode 题目的目的是练习写 function,因此这种方式在 Leetcode里无法通过,但我...
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN DECLARE P INT DEFAULT N-1; IF (P<0) THEN RETURN NULL; ELSE RETURN ( # Write your MySQL query statement below. SELECT IFNULL( ( SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC ...
题目链接:https://leetcode.com/problems/nth-highest-salary/description/ 题意:查询出表中工资第N高的值 思路: 1、先按照工资从高到低排序(注意去重) selectdistinctSalaryfromEmployeeorderbySalarydesc 1. 2、使用rownum给查询出的数据标注行号 selectrownum ro,s.Salaryfrom(selectdistinctSalaryfromEmployeeorderby...
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. 1. Accepted Solution This was my accepted solution for this LeetCode problem: CREATE FUNCTION getNthHighestSalary(N INT) RETURNS ...
2.是工资的第N位,所以需要在select了工资列之后用group by 3.行号从0起,因而我们需要选择的行号为N-1 CREATEFUNCTIONgetNthHighestSalary(NINT)RETURNSintBEGINSETN=N-1;RETURN(SELECTSalaryFROMEmployeeGROUPBYSalaryORDERBYSalaryDESClimit1offsetN);END
其实Leetcode 上还有一道选第第二大元素的题目,不过和这道题实质上一毛一样,所以就只记录这一个题目了。 选第N大的元素思路已经很明朗了,由于 sql 内置的函数不存在选第N大元素的函数,所以我们只能另辟蹊径了,就根据Salary排序排序,然后用offset选排序后的第N个元素,最后用LIMIT限制选择一个元素,就是第N大的...