在单个查询中,我们如何从sql中的表中获取第一,第三和第五高值。 [英] On a single query, how we get first, third and fifth highest value from a table in sql.
本文介绍了在单个查询中,我们如何从sql中的表中获取第一,第三和第五高值。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
仅使用单个查询,我们如何从'salary_table'获得第一,第三和第五最高薪水。
解决方案
更轻松的解决方案,无需CTE:
SELECT 名称,薪水 FROM
(
SELECT ROW_NUMBER() OVER (订单 按 salary desc ) as rowNr,name,salary
From tblSalary
) as r
其中 r.rowNr in ( 1 , 3 , 5 )
< blockquote>请按照以下链接..
http://www.programmerinterview.com/index.php/database-sql/find- nth-highest-salary-sql / [ ^ ]
sql查询为第n个最高&员工姓名最低的工资 [ ^ ]
我希望它对你有所帮助..
AARIF SHAIKH
试试这样如果您使用的是Sql Server 2008 R2,它会工作,
我正在考虑表名称为Table1,样本列名称为Name,Column2,Column3。
cte as ( select a.Name,a.Column2,a.Column3,MAX(a.Salary) as Max_Salary,ROW_NUMBER() over ( order by a.Name) Rn 来自 表 as a group by a.Name,a.Column2,a.Column3)选择 * 来自 cte 其中 Rn 中的code-keyword>( 1 , 3 , 5 )
已检查并且有效,
希望它会是对你有帮助。
Only using a single query, how we get first, third and fifth highest salary together from a 'salary_table'.
解决方案
easier solution without CTE:
SELECT Name, Salary FROM ( SELECT ROW_NUMBER() OVER (order by salary desc) as rowNr, name, salary From tblSalary ) as r where r.rowNr in (1,3,5)
Please follow below links..
http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/[^]
sql query for nth highest & lowest salary with employee name[^]
I hope it will be helpful for you..
AARIF SHAIKH
Try like this If you are using Sql Server 2008 R2 , It'll work,
And i am considering table name as Table1 and sample column name as Name,Column2,Column3.
with cte as (select a.Name,a.Column2,a.Column3,MAX(a.Salary) as Max_Salary,ROW_NUMBER() over(order by a.Name) as Rn from Table as a group by a.Name,a.Column2,a.Column3 ) select * from cte where Rn in (1,3,5)
Checked and it works,
Hope it will be helpful to you.
这篇关于在单个查询中,我们如何从sql中的表中获取第一,第三和第五高值。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文