SQL 查询 班级总成绩 前三名,总成绩有相等的情况

 
 1.需求描述:  查询   班级总成绩   前三名,总成绩有相等的情况,所以会出现同时获得名次。
 
 2.运行环境:  Winows 7 旗舰版,SQLServer 2008 R2
 
 3.解决方案:  分别用  Union,Select+Case,  Declare变量,三种方法查询
 
 4.具体编码:
 



drop
table Record go create table Record ( Student_No int identity(1,1) primary key, Student_Name varchar(20), Chinese float, Math float, English float, totalScore as Chinese+Math+English ) go insert into Record(Student_Name,Chinese,Math,English) values('刘德华',100,80,80) insert into Record(Student_Name,Chinese,Math,English) values('郭富城',100,100,60) insert into Record(Student_Name,Chinese,Math,English) values('施瓦辛格',100,70,70) insert into Record(Student_Name,Chinese,Math,English) values('刘志玲',100,90,50) insert into Record(Student_Name,Chinese,Math,English) values('终结者',100,90,50) insert into Record(Student_Name,Chinese,Math,English) values('兰博',88,54,39) insert into Record(Student_Name,Chinese,Math,English) values('史泰龙',98,89,14) insert into Record(Student_Name,Chinese,Math,English) values('朱莉娅',10,42,10) insert into Record(Student_Name,Chinese,Math,English) values('罗素',10,76,66) insert into Record(Student_Name,Chinese,Math,English) values('毕达哥拉斯',18,98,78) insert into Record(Student_Name,Chinese,Math,English) values('莱布尼茨',87,98,45) insert into Record(Student_Name,Chinese,Math,English) values('牛顿',85,85,45) insert into Record(Student_Name,Chinese,Math,English) values('柏拉图',12,78,89) go -----------------------第一种方法--------------------------------- select '第一名',* from Record where totalScore in ( select top 1 a.totalScore from ( select distinct totalScore from Record )a order by a.totalScore desc ) union select '第二名',* from Record where totalScore in ( select MIN(t.totalScore) from ( select top 2 a.totalScore from ( select distinct totalScore from Record )a order by a.totalScore desc ) t ) union select '第三名',* from Record where totalScore in ( select MIN(t.totalScore) from ( select top 3 a.totalScore from ( select distinct totalScore from Record )a order by a.totalScore desc )t )

-------------------第二种方法--------------------------
select * from( select Student_No,Student_Name,Chinese,Math,English,totalScore, case when totalScore=(select top 1 totalScore from (select distinct totalScore from Record) t order by totalScore desc) then '第一名' when totalScore=(select min(totalScore) from (select top 2 totalScore from (select distinct totalScore from Record) t order by totalScore desc)t2) then '第二名' when totalScore=(select min(totalScore) from (select top 3 totalScore from (select distinct totalScore from Record) t order by totalScore desc)t3) then '第三名' else null end as '排名' from Record )R where R.排名 is not null order by totalScore desc

-------------------第三种方法-----------------
declare @max float
set @max=(select top 1 totalScore from (select distinct totalScore from Record) t   order by totalScore desc)
declare @min float
set @min=(select min(totalScore) from (select top 3 totalScore from (select distinct totalScore from Record) t   order by totalScore desc)t2)

select Student_No,Student_Name,Chinese,Math,English,totalScore,
       case 
           when totalScore=@max then '第一名'
           when totalScore<@max and totalScore>@min then '第二名'
           when totalScore=@min then '第三名'
           else '无名'
           end as '排名'
           from Record
           order by totalScore desc
-----------------case第二种用法---------------------------
declare @mid float
set @mid=(select min(totalScore) from (select top 2 totalScore from (select distinct totalScore from Record) t   order by totalScore desc)t2)
select Student_No,Student_Name,Chinese,Math,English,totalScore,
       case totalScore
           when @max then '第一名'
           when @mid then '第二名'
           when @min then '第三名'
           else '无名'
           end as '排名'
           from Record
           order by totalScore desc
           

查询结果:

抛砖引玉,能用其他算法   算出第四名,第五名,第六名。。。。。一直到100名

 

有四位高手提供了其他解决方案

  高手 1:土豆烤肉

SELECT a.*,b.RankOrder,'' + CONVERT(NVARCHAR(4),b.RankOrder) + ''
FROM Record a left JOIN
(
    SELECT ROW_NUMBER() OVER(ORDER BY TotalScore DESC) AS RankOrder,TotalScore
    FROM
    (
        SELECT DISTINCT TotalScore
        FROM Record
    ) temp
) b ON A.totalScore = b.totalScore
ORDER BY totalScore DESC

高手 2:小晓文盲

select *,'' +convert(varchar,dense_rank() over( order by totalScore desc))+ '' 
from Record
order by totalScore desc

两高手方法做了一个执行计划,下面是效果

高手3:灵X风

Create table #temp1(id int identity(1,1),Score int)

INSERT INTO #temp1(Score) SELECT DISTINCT totalScore FROM Record ORDER BY totalScore DESC

SELECT Record.*,''+convert(varchar,id)+'' FROM Record LEFT JOIN #temp1
ON totalScore = Score
ORDER BY id
DROP TABLE #temp1



高手 4: JeffWong

WITH ScoreInfo AS (
SELECT [Student_No], [Student_Name],[Chinese],[Math],[English],[totalScore]
      ,DENSE_RANK() OVER(ORDER BY totalScore DESC) AS 'R'
FROM [Record] )
SELECT  [Student_No], [Student_Name],[Chinese],[Math],[English],[totalScore] ,''+ CONVERT(varchar,R)+''
FROM ScoreInfo 

posted @ 2012-11-07 00:36  正定聚  阅读(10188)  评论(15编辑  收藏  举报