mysql |那些关于第二大的事

经常遇到关于第二大的计算，如找到销售量第二大的id等等这类问题。

一下是我的学习总结，有些来自网络，出处会给出详细的链接，方便寻找源码。

---

题目 ：第二高的薪水

来源https://leetcode.com/problems/second-highest-salary/description/

方案一

SELECT    (SELECT DISTINCT            Salary        FROM            Employee        ORDER BY Salary DESC        LIMIT 1 OFFSET 1) AS SecondHighestSalary;

保证了Salary只有唯一的输出，避免多个第二高的情况。

方案二

SELECT    IFNULL(      (SELECT DISTINCT Salary       FROM Employee       ORDER BY Salary DESC        LIMIT 1 OFFSET 1),    NULL) AS SecondHighestSalary

解决了为空的问题，为空就输出NULL

方案三：

SELECT MAX(Salary) AS SecondHighestSalary     FROM Employee          WHERE Salary < (SELECT MAX(Salary) FROM Employee)

参考http://blog.csdn.net/u010479690/article/details/25053937

为了方便自己日后参看，将http://blog.csdn.net/u010479690/article/details/25053937中的内容转过来。

拓展

mysql

表中的第二大id号

SELECT MAX(vcid) FROM msdtb1701      WHERE vcid < (SELECT MAX(vcid) FROM msdtb1701)

或

SELECT  MAX(vcid) FROM msdtb1701      WHERE vcid NOT IN (SELECT MAX(vcid) FROM msdtb1701)

表中的第二大id号及其user

SELECT vcuser ,vcid FROM msdtb1701      WHERE vcid=            (SELECT MAX(vcid) FROM msdtb1701 WHERE vcid                     NOT IN (SELECT MAX(vcid) FROM msdtb1701))

表中第三大id号

SELECT MAX(vcid) FROM  msdtb1701      WHERE vcid < (SELECT  MAX(vcid) FROM msdtb1701 WHERE vcid               NOT IN (SELECT MAX(vcid) FROM msdtb1701))

表中前三大的id号及其user

SELECT vcid,vcuser FROM msdtb1701  ORDER BY vcid DESC LIMIT 3

注意：依照此数值调整限制输出行数

第N大的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGINDECLARE M INT;SET M=N-1;  RETURN (      # Write your MySQL query statement below.      SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1  );END

Sql Server

表中第N大数值

select TOP N * from tablename where

ORACLE

表中第N大的数值

SELECT * FROM TABLE1 WHERE ROWNUM<=N