第三高的薪水SQL
我尝试从顶部5名员工获得所有3最高的薪水这样第三高的薪水SQL
salary
35000
34000
20000
12000
500
40000
25000
41000
90000
550000
查询
select top 5
(SELECT MAX(grosssalary) FROM Detail) maxsalary ,
(SELECT MAX(grosssalary) FROM Detail) sec_max_salary,
(SELECT MAX(grosssalary) FROM Detail
WHERE grosssalary NOT IN (SELECT MAX(grosssalary) FROM Detail)) as third_max_salary
但这样表示
maxsalary sec_max_salary third_max_salary
550000 550000 41000
,其中数据因为我想要这样的数据
maxsalary sec_max_salary third_max_salary
550000 90000 41000
做一个CTE
并获得salary DESC
的ROWNUMBER()
和外部查询获取与ROWNUMBER等于记录3
;WITH CTE AS
(
SELECT RN = ROW_NUMBER() OVER (ORDER BY salary DESC),
Salary
FROM [YourTable]
)
SELECT Salary
FROM CTE
WHERE RN <= 3
注:如果你想第三高的薪水使用RN=3
如果你希望所有顶部3薪水然后用RN<=3
如果你想前3名的薪水最高,那么你可以这样做,以及:
SELECT TOP 3 Salary
FROM [YourTable]
ORDER BY Salary DESC
的副本什么是CTE?这是复杂的查询请根据我的查询 –
@superuser修改这将给你前3名的薪水,但他们仍然可以都是相同的薪水。如果你正在寻找前3名不同的薪水,那么你所需要做的就是将ROW_NUMBER()转换为DENSE_RANK()。另外请注意使用PIVOT这个输出会让你很好的3行而不是行。 – Matt
使用DENSE_RANK()
订购工资,然后使用CASE
表达式查找前3名工资。
查询
;with cte as(
select [sal_rank] = dense_rank() over(
order by [grosssalary] desc
), *
from [your_table_name]
)
select max(case [sal_rank] when 1 then [grosssalary] end) as [maxsalary],
max(case [sal_rank] when 2 then [grosssalary] end) as [sec_max_salary],
max(case [sal_rank] when 3 then [grosssalary] end) as [third_max_salary]
from cte;
CREATE TABLE #A
(
salary INT
)
INSERT INTO #A VALUES
(35000),
(34000),
(20000),
(12000),
(500 ),
(40000),
(25000),
(41000),
(90000),
(550000)
select [1] maxsalary, [2] sec_max_salary, [3] third_max_salary
from (
SELECT *
FROM (
SELECT *,
RN = ROW_NUMBER() OVER (ORDER BY Salary DESC)
FROM #A
)a
WHERE RN <= 3
) src
pivot
(
MAX(SALARY)
for RN in ([1], [2], [3])
) piv;
输出
maxsalary sec_max_salary third_max_salary
550000 90000 41000
如果您正在使用SQL SERVER 2012+
然后用OFFSET
SELECT TOP 1 salary,
(SELECT salary FROM Yourtable
ORDER BY salary DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY),
(SELECT salary FROM Yourtable
ORDER BY salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY)
FROM Yourtable
ORDER BY salary DESC
如果您想只是第三高的薪水,然后
SELECT salary
FROM yourtable
ORDER BY salary DESC
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY
select *
from
(select *
from table
order by salary desc
limit 3
) as top_three
order by top_three.salary asc
limit 1
sql-server使用SELECT TOP不限制 – Matt
下面的代码会导致你得到第N最大或最小的薪水值不考虑到数据类型,即。整数或Vearchar。 只需更改'<'符号以获取更小的第n个值。
select * from txt_salary as t1 where 'nth largest' =
(select count(salary) from txt_salary as t2 where t1.salary<=t2.salary)
对于SQL SERVER
SELECT E1。* FROM emp
E1 WHERE e1.salary = ( 选择MIN(e2.salary) 从(选择顶部3 DISTINCT(E3。薪水)从emp
如通过e3.salary降序E3 ORDER)为E2 )
对于MySQL
SELECT e1。* FROM emp
E1 WHERE e1.salary = ( 选择MIN(e2.salary) 从(由e3.salary降序极限0,3)为E2从emp
选择DISTINCT(e3.salary)作为E3 ORDER )
选择最大值(工资),如从雇员
UNION
选择最大值(工资)最高为从雇员2nd_highest其中薪水<(选择最大(从雇员薪水))
UNION
最大值(工资),如从雇员3rd_highest其中工资<(选择员工最大(工资)的地方Ý<(选择最大(从雇员薪水)))=
对于第十八最高薪水=
选择工资,ENAME,EID FROM(选择e.ename,e.salary,e.eid,秩() OVER(按e.eid desc排序)等级)WHERE等级= 18
可能的重复:http://*.com/questions/16234983/how-to-find-third-or-nth-maximum-salary-from -salary- – thebenman
可以检查我的答案@superuser – Chanukya
http://*.com/q/16234983/3261852 – Munavvar