176.Second Highest Salary
Problem Link
找出工资数额第二的值．
想法１: 如果比某一条工资大的字段的计数恰恰为１，那么一定是工资排在第二的．
Code:
This version get accepted, but note that WITHOUT IFNULL there will
arise a mistake if there is only one line in the table (nothing is not equal to null in MySQL)
# Write your MySQL query statement below
SELECT IFNULL((SELECT DISTINCT Salary FROM Employee AS E1
WHERE (SELECT COUNT(*) FROM Employee AS E2
WHERE E1.Salary < E2.salary)=1), NULL) AS SecondHighestSalary
think2:
Select MAX(Salary) AS SecondHighestSalary from Employee
where Salary < (Select MAX(Salary) from Employee)
Nth Highest Salary
thinking:
和上一题的想法类似（其实单纯解决上一题可以不用那么麻烦，直接想法２就可以了，但是想法１更具有移植性，在这一题就有体现）．这种想法可以概括为：对每一条工资，如果比你大的有Ｎ-1,那么这条工资记录就是第N大的（注意COUNT 中的DISTINCT 是不可少的）
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN(
# Write your MySQL query statement below.
SELECT IFNULL((SELECT DISTINCT Salary FROM Employee AS E1
WHERE (SELECT COUNT(DISTINCT Salary) FROM Employee AS E2
WHERE E1.Salary < E2.salary)=N-1), NULL) AS SecondHighestSalar
);
END
现在我们来思考这种方式的弊端，好像不是很快？
such code bellow will be much more faster than your version:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN(
# Write your MySQL query statement below.
SELECT e1.Salary
FROM (SELECT DISTINCT Salary FROM Employee) e1
WHERE (SELECT COUNT(*) FROM (SELECT DISTINCT Salary FROM Employee) e2 WHERE e2.Salary > e1.Salary) = N - 1
LIMIT 1
);
END
Analysis: In my code, Table e1, e2 are full tables of Employee, however in the second version of codes, e1,e2 are selected table(by DISTINCT) and the table will be much more smaller.
