在表中获得任何最高价值 [英] get any hightest value in table
本文介绍了在表中获得任何最高价值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我们如何在桌子上获得最高价值。
桌子:
id工资
1 300
2 350
3 400
4 500 >
8 200
9 450
< span class =code-keyword>< pre lang = css > 跨度> EG。获得第三高薪;
id工资
3 400
< pre lang = css > 例如。获得第二高薪;
id salary
3 450 < / pre >
等
在MSSQL中查询
解决方案
你必须使用执行功能 [ ^ ]。试试!
- 得到1.最高工资
SELECT *
FROM (
SELECT *,ROW_NUMBER() OVER ( ORDER BY 薪资 DESC ) As RowNo
FROM TableName
) AS T
< span class =code-keyword> WHERE RowNo = 1
怎么样
<前lang =SQ L> SELECT TOP 3 FROM salaryTable ORDER BY salary DESC
结果:
4 500
9 450
3 400
2 350
1 300
8 200
然后使用相应索引的结果数据记录
0 =最高
1 =第二高
2 =第三高
等......
(如果你想要超过3条记录改变sql查询)
你可以像这样使用CTE作为临时表,比如第三高:
使用cte0(id,salary)
AS
(
从表名顺序中选择前3 *按工资DESC
)
从cte0中选择前1 *
了解更多: SQL SERVER 2008中的公用表表达式(CTE) [ ^ ]
How we can get any highest value in table.
table:
id salary
1 300
2 350
3 400
4 500
8 200
9 450
<pre lang="css">Eg. get 3rd high salary;
id salary
3 400
<pre lang="css">Eg. get 2rd high salary;
id salary
3 450</pre>
etc.
Query in MSSQL
解决方案
You have to use ranking functions[^]. Try!
--get 1. highest salary SELECT * FROM( SELECT *, ROW_NUMBER() OVER(ORDER BY Salary DESC) As RowNo FROM TableName ) AS T WHERE RowNo=1
See past past questions[^] too.
how about
SELECT TOP 3 FROM salaryTable ORDER BY salary DESC
Result: 4 500 9 450 3 400 2 350 1 300 8 200
then use the data record of the result with the appropriate index
0 = highest
1 = second highest
2 = third highest
and so on...
(if you want more than 3 records change the sql query)
You can use a CTE as temp table like this, say 3rd highest:
With cte0(id, salary) AS ( select top 3 * from tablename order by salary DESC ) select top 1 * from cte0
learn more: Common Table Expressions(CTE) in SQL SERVER 2008[^]
这篇关于在表中获得任何最高价值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文