SQL查询问题 [英] Sql Query Question

问题描述

我有带字段的表
1. SalaryID
2.工资金额

我想使用查询从表中获得第二高薪水.

i have table with fields
1. SalaryID
2.Salary Amount

i want to get second highest salary from my table using query.

推荐答案

select * from salary where salamount not in (select max(salamount) from salary)

我没有认为这样的SQL可以工作以进行测试；

I Don''t Have SQL to hand to test by think something like this would work;

Select Top 1 * From ( Select Top 2 * From Salaries Order By Salary Desc) Order By Salary Asc;

从理论上讲，哪一个应该给您第二高的薪水，首先创建一组最高的2薪水，然后翻转顺序并返回最低的薪水.

Which should in theory give you the 2nd highest salary, by first creating a set of highest 2 salaries and then flipping the order and returning the lowest.

这是获取N-如果使用MS Sql Server 2005/2008，则薪水最高/最低

Here is a code to get the N-th highest/lowest salary if you use MS Sql Server 2005/2008

-- N-th highest salary
SELECT  TOP 1
        SalaryAmount
FROM    (
        SELECT  SalaryRank  = ROW_NUMBER() OVER(ORDER BY SalaryAmount DESC),
                SalaryAmount
        FROM    Salary
        ) a
WHERE   SalaryRank = 2 --N

-- N-th lowest salary
SELECT  TOP 1
        SalaryAmount
FROM    (
        SELECT  SalaryRank  = ROW_NUMBER() OVER(ORDER BY SalaryAmount),
                SalaryAmount
        FROM    Salary
        ) a
WHERE   SalaryRank = 2 --N

希望有帮助！

Hope this help!

这篇关于SQL查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！