1. excel中sql如何找出考试成绩总分前5名的同学
可以用row_number函数来解决。
1、创建测试表，插入数据：
createtablesc(idint,namevarchar(20),classvarchar(20),scoreint);insertintoscvalues(1,'badkano','一年一班',100)insertintoscvalues(2,'网络知道团长','一年一班',99)insertintoscvalues(3,'小短','一年一班',95)insertintoscvalues(4,'小小动','一年一班',97)insertintoscvalues(5,'小智','一年一班',80)insertintoscvalues(6,'吕布','一年二班',67)insertintoscvalues(7,'赵云','一年二班',90)insertintoscvalues(8,'典韦','一年二班',89)insertintoscvalues(9,'关羽','一年二班',70)insertintoscvalues(10,'马超','一年二班',98)
2、查询每个班级的前五名，可用语句：
select*from(selectrow_number()over()排名,*fromsc)twhere排名<=3orderbyclassasc,scoredesc
3、结果截图：
2. SQL查询语句： 查询 每科目分数最高的5项(学生姓名，科目，分数)
sql server 2005及以上版本如下，sql server 2000的话得用别的方法了
declare @t table (
sname varchar(30)
,ssubject varchar(30)
,score int
)
insert into @t
select '姓名1' as n,'科目1' as s,'95' as sc union all
select '姓名2' as n,'科目1' as s,'83' as sc union all
select '姓名3' as n,'科目1' as s,'90' as sc union all
select '姓名4' as n,'科目1' as s,'75' as sc union all
select '姓名7' as n,'科目1' as s,'71' as sc union all
select '姓名8' as n,'科目1' as s,'95' as sc union all
select '姓名9' as n,'科目1' as s,'86' as sc union all
select '姓名10' as n,'科目1' as s,'73' as sc union all
select '姓名11' as n,'科目1' as s,'76' as sc union all
select '姓名13' as n,'科目1' as s,'96' as sc union all
select '姓名14' as n,'科目1' as s,'73' as sc union all
select '姓名15' as n,'科目1' as s,'77' as sc union all
select '姓名6' as n,'科目2' as s,'88' as sc union all
select '姓名7' as n,'科目2' as s,'64' as sc union all
select '姓名8' as n,'科目2' as s,'91' as sc union all
select '姓名9' as n,'科目2' as s,'66' as sc union all
select '姓名12' as n,'科目2' as s,'69' as sc union all
select '姓名13' as n,'科目2' as s,'93' as sc union all
select '姓名14' as n,'科目2' as s,'90' as sc union all
select '姓名15' as n,'科目2' as s,'67' as sc union all
select '姓名18' as n,'科目2' as s,'65' as sc union all
select '姓名19' as n,'科目2' as s,'78' as sc union all
select '姓名20' as n,'科目2' as s,'88' as sc union all
select '姓名21' as n,'科目2' as s,'96' as sc union all
select '姓名1' as n,'科目3' as s,'77' as sc union all
select '姓名2' as n,'科目3' as s,'79' as sc union all
select '姓名4' as n,'科目3' as s,'84' as sc union all
select '姓名5' as n,'科目3' as s,'71' as sc union all
select '姓名9' as n,'科目3' as s,'76' as sc union all
select '姓名10' as n,'科目3' as s,'61' as sc union all
select '姓名11' as n,'科目3' as s,'63' as sc union all
select '姓名12' as n,'科目3' as s,'77' as sc union all
select '姓名13' as n,'科目3' as s,'69' as sc union all
select '姓名14' as n,'科目3' as s,'89' as sc union all
select '姓名19' as n,'科目3' as s,'94' as sc union all
select '姓名20' as n,'科目3' as s,'92' as sc union all
select '姓名21' as n,'科目3' as s,'82' as sc union all
select '姓名22' as n,'科目3' as s,'65' as sc union all
select '姓名23' as n,'科目3' as s,'63' as sc union all
select '姓名26' as n,'科目3' as s,'83' as sc
--这是查询语句，把@t改成自己实际表名，字段改成实际字段名
select sname
,ssubject
,score from (
SELECT sname
,ssubject
,score
,row_number() over (PARTITION by ssubject order by score desc) as gorder
FROM @t
) as a
where gorder < 6
3. Access SQL语言怎么选择每个班级的前五名分数
在access中不好做，你将数据导出到Excel中，然后插入数据透视表，就会轻松搞定。
4. SQL如何处理分数排名
SQL如何处理分数排名
SELECT top 5
FROM 表名
ORDER BY 字段名 DESC
5. 用SQL列出数据库成绩的前五名学生的学号、姓名、成绩
一楼的：
select student.学号,姓名,成绩 FROM STUDENT,SCORE WHERE STUDENT.学号=SCORE.学号 ORDER BY 成绩 DESC TOP 5 (本语句适合二级) 追问(急！！！)找出各科成绩均在85分以上（含）的学生的学号、姓名 这个呢？ 回答完就给分^-^
回答追问的：
select student.学号,姓名,min(成绩) 最低分,max(成绩) 最高分 FROM STUDENT,SCORE
WHERE STUDENT.学号=SCORE.学号
group by student.学号,姓名
having min(成绩)>=85
6. sql取出每个分组的前几条
一个简单的方法就是排序之后给它分配序号，根据序号去找前几条
select*
from(select公司,人数,
--按人数从多到少分配序号，不同公司会重1开始
rank()over(partitionby公司orderby人数desc)asxh
from公司档案)asA
wherexh<=2--取前几位就写几
orderby公司,人数desc
排序效果如下
7. sql server 2005 查找并统计前5名数量最多的类型
SELECT top 5 aType,count(*) as numbers FROM Articles
where aType in(6,7,8,9,10)
group by aType
order by numbers DESC
8. 取每个班前5名成绩的sql怎么写
--单纯通过top是不行的，要用到row_number()over()才能取到每个班的分别的前5名
select*from(selectt.*，row_number()over(partitionby班级orderby成绩desc)asfnumfrom表名T)t1wherefnum<=5
9. sql 语句查询 前5名后5名的成绩
不知道你的是什么数据库，我用oracle的写法了。
两种办法：
分别求最大和最小，然后union all
select * from（select* from table order by 成绩） where rownum<=5
union all
select * from（select* from table order by 成绩 desc） where rownum<=5
利用排序，找到每个人的位置，然后输出。排序的方法很多，可以用rownum排序，也可以用row_number()over()排序
我用row_number()over()写一个
selecta.姓名,a.成绩 from
(select row_number()over(order by 成绩) num,姓名,成绩 from table) a where a.num<=5 or
a.num>=(select count(*)-5 from table)
我没实验，不过就算有问题也应该不大。
sqlserver的版本是啥？是2005以上么？如果是
那么利用那个row_number的应该也可以，不过为了让分数一样的人都出来，那么最好改为
这种情况是假设前五出现分数相同的话，假如前五名有六个人的情况，不过我没有输出名次，另外我上面的那种写法也可以试试，sqlserver好像也可以。
不过如果你要是sql2005以前的版本那时sqlserver还没有这几个开窗函数，那就稍微有点麻烦了。
select姓名,成绩 from
select rank()over(order by 成绩) num,rank()over(order by 成绩 desc) num_desc,姓名,成绩 from table) a where a.num<=5 or a.num_desc<=5 order by 成绩 desc
10. SQL语句：十个班里各取前五名
--创建测试数据
declare @t table(ID int,xm varchar(20),total int,banji varchar(50))
insert @t
select 1,'张一',100,'A班' union all
select 2,'张二',200,'A班' union all
select 3,'张三',300,'A班' union all
select 4,'张四',400,'A班' union all
select 5,'张五',500,'B班' union all
select 6,'张六',600,'B班' union all
select 7,'张七',700,'B班' union all
select 8,'张八',800,'B班' union all
select 9,'张九',900,'C班' union all
select 10,'张十',1000,'C班' union all
select 11,'张十一',1100,'C班' union all
select 12,'张十二',1200,'C班'
--查询
select * from @t a
where (select count(*) from @t b where b.banji = a.banji and b.total > a.total) < 3
--这里是前三名，前五名只需把这里的<3改为<5即可
--结果
/*
ID xm total banji
---------------------------------------------
2 张二 200 A班
3 张三 300 A班
4 张四 400 A班
6 张六 600 B班
7 张七 700 B班
8 张八 800 B班
10 张十 1000 C班
11 张十一 1100 C班
12 张十二 1200 C班
*/