Leetcode DB -1

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.