LeetCode - Nth Highest Salary 题目大概意思是要求找出第n高的Salary,直接写一个Function。作为一个SQL新手我学到了1.SQL中Function的写法和变量的定义,取值。2.limit查询分 页的方法。 在这个题目中limit n-1, 1是从n-1开始取出一条数据。这样说比较直观。 1 2 3 4 5 6 7 8 9 CREATEFUNCTIONgetNthHi...
一、表信息 Table: Employee 二、题目信息 找出工资第 N 高的员工的薪水。 Write a SQL query to get the nth highest salary from the Employee table. 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 s...
编写一个SQL查询语句,获取Employee表中第n高的薪水(Salary)。 创建表和数据 CreatetableIfNotExistsEmployee (Id int, Salaryint);TruncatetableEmployee;insertintoEmployee (Id, Salary)values('1','100');insertintoEmployee (Id, Salary)values('2','200');insertintoEmployee (Id, Salary)values('3','300...
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN RETURN ( # Write your MySQL query statement below. SELECT A.salary FROM (SELECT DISTINCT Salary FROM Employee) A, (SELECT DISTINCT Salary FROM Employee) B WHERE B.salary >= A.salary GROUP BY A.salary HAVING COUNT(B.salary) =N ...
题中已经写好了一个函数,我们需要将查询的SQL语句写在注释下面,然后返回结果。 CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN RETURN ( # Write your MySQL query statement below. );END 1. 参考1: CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGINDECLARE M INT;SET M=N-1; ...
选第N大的元素思路已经很明朗了,由于 sql 内置的函数不存在选第N大元素的函数,所以我们只能另辟蹊径了,就根据Salary排序排序,然后用offset选排序后的第N个元素,最后用LIMIT限制选择一个元素,就是第N大的那个元素了。 本来我的解法是这样的: CREATEFUNCTIONgetNthHighestSalary(NINT)RETURNSINTBEGINDECLAREMINT;SETM...
Finding record of Nth highest salaried employee (where N, should equal to records or less then of the records), Here we are finding 1st , 2nd, 3rd and so on highest salaried employee’s name one by one using SQL Query. Here employee table, which has three fields eid(employee id), ...
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...
nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null. 题目解答: CREATEFUNCTION getNthHighestSalary(NINT)RETURNSINT BEGIN DECLARE MINT; SETM=N-1;
Solution For SQL Server 2000, to get highest 2nd salary, we will use the query given below. SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 2 salary FROM employee ORDER BY salary DESC) a ORDER BY salary This will give only salary value. Kindly find the query to get all the ...