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