SQL Server找到2等级 [英] Sql server find 2 rank

问题描述

这是我的餐桌学生如何找到这张桌子的2个等级
您是否可以为该表编写查询而无需总计如何查找等级

Hi , this my table student how to find 2 rank of this table
can you write query for this table without total how to find rank

studId studName mark1  mark2   mark3
S001	Raja	60	70        40
S002	Siva	40  	79        77
S003	Kumar	80	30        90
S004	Mani	60	70        40
-------------------------------------

推荐答案

如果您不想在表中插入总和，则在查询本身中添加它..
使用下面的查询，您将获得排名第二的学生的详细信息，而无需在表中存储总和..

if you don''t wanna insert a sum into table then.. made it in query itself..
using below query you will get 2nd rank students details without store a sum in your table..

select * from exam where (mark1+mark2+mark3) = select max(mark1+mark2+mark3) test From eaxm where (mark1+mark2+mark3)  not in (select max(mark1+mark2+mark3)  test From exam)

HI ,,,,

请尝试以下查询

HI ,,,,

Please try the below query

with cte_ex as 
(select distinct studId,studName,(mark1+mark2+mark3) as sumt,ROW_NUMBER() over (order by (select 1 ))as rowno from tmpstd)
select  A.studId,A.studName,A.sumt  from cte_ex A
join cte_ex B on A.rowno=B.rowno 
where A.rowno=2

我给你最高2个最高薪水示例

选择最大值(sal)
来自Emp
薪水不在的地方(从Emp中选择排名前1的薪水)

I am giving you max 2 highest salary example

Select max(sal)
from Emp
where sal not in (Select top 1 sal from Emp)

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