SQL练习-中式排名

JLxiangwei

******************************************************************
先开贴，答案稍后整理

?﹎尐蜻蜓.o0

勉强写成这样了 等着你的答案！

1. select * from (select a.班级,a.姓名,a.总成绩, sum(1) as 名次 from
   
2. (select 班级,姓名,sum(成绩) as 总成绩 from [Sheet1$a:c] group by 班级,姓名)a,
   
3. (select 班级,姓名,sum(成绩) as 总成绩 from [sheet1$a:c] group by 班级,姓名)b,
   
4. (select 班级,sum(成绩) as 总成绩 from [sheet1$a:c] group by 班级)c
   
5. where a.班级 = b.班级 and a.总成绩 <=b.总成绩 and a.班级 = c.班级
   
6. group by a.班级,a.姓名,a.总成绩,c.总成绩)
   
7. order by 班级,名次

复制代码

效果如下图：

xiaoni

先想想，好题目

sklzz888

用函数还是用VB？

9lee

连抄带蒙

1. select a.*,(select count(*) from (select distinct 班级, 成绩 from [Sheet1$a:c]) b where b.班级=a.班级 and b.成绩>=a.成绩) as 名次
   
2. from [Sheet1$a:c] a
   
3. 
   
4. 
   

复制代码

一杯清荼

1. select (select count(*) from (select 班级,成绩 from [Sheet1$a:c] group by 班级,成绩)a where a.班级=b.班级 and a.成绩>=b.成绩) AS 名次,* from [Sheet1$a:c]b

复制代码

望天打卦

姓名没有整好，重在参与吧

sklzz888

sklzz888 发表于 2013-10-18 13:08
用函数还是用VB？

{:35:}

wyhdp

貌似不错的应用，可惜SQL没学好！

tgydslr

好好学SQL