sql查询各科成绩最好的学生

1. 用sql 查询出各个科目中成绩最好的学生的名字

select
姓名
from
(select
*
from
(
select
a.学生编号,a.姓名
b.
学生编号,b.科目,b.分数
from
table1
a
right
join
table2
b
on
b.学生编号=a.学生编号
)c
group
by
c.科目
having
max(c.分数)
)

2. SQL 怎样查询 单科成绩排名第3名的学生

写个笨点的方法回，答
SELECT * INTO #TempA FROM score ORDER BY degree DESC
SELECT TOP 1 * FROM #TempA WHERE degree NOT IN(SELECT TOP 2 degree FROM #TempA )

3. 在数据库查询中查询各门课程取得最高成绩的学生姓名和成绩

select
t.sno,sname,avg(grade)
as
平均分,count(cname)
as
选课门数
from
student
t,sc
c,course
e
where
t.sno=c.sno
and
c.cno=e.cno
and
t.sno=
(select
top
1
t1.sno
from
student
t1,sc
c1,course
e1
where
t1.sno=c1.sno
and
c1.cno=e1.cno
and
e1.cname='数据结构'
order
by
c1.grade
desc)
group
by
t.sno,sname
1.
先用子查询
查出课程最高分的同学的学内号容
2.
再根据学号得到平均分agv和选课门数
count

4. sqlserver查询各系各科成绩最高分的学生的学号，姓名，系名，课程名称，成绩

select
a.sno学号抄,a.sname姓名袭,a.sdept系名,c.cname课程名称,b.maxgrade成绩
from
studenta
innerjoin(selectcno,max(sno)sno,max(grade)maxgradefromscgroupbycno)bona.sno=b.sno
innerjoincourseconb.con=c.cno

5. sql语句查询横排成绩表中成绩最好的学生姓名、科目和成绩

/*
让我们假设 这个表叫ExamResults.
name - 姓名
subjects - 科目内
grades - 成绩容
*/
--then the query is as following.
select
er1.name, er1.subjects, er1.grades
from ExamResults as er1, ExamResults as er2
where er1.name = er2.name and er1.grades > er2.grades

6. mysql中一张学生表，查询出单科成绩前十名学生的所有信息 和总分成绩前十名学生的所有信息 在线等

学生表名为：student, 单科成绩的字段为：subject。学生名字为：name

查询单科成绩前十：mysql语句为：select * from student order by subject desc limit 10;

查询总分成绩前十：mysql语句为：select sum(subject) subject,name from student group by name order by subject desc limit 10;

注：

select sum(subject) subject,name

这句的意思是：sum(subject) subject 把单科成绩加总用subject 来命名（就是总成绩），name就是一个字段，这里只有两个字段。

group by name order by subject ： group by name 的意思按照名字这一栏来分组，当然，学生成绩表名字有可能是一样的，按照学号是最准确的，这里只是举个例子。

order by subject 这句的意思是按照总分成绩排序，这里的subject 是前面重命名总分的意思。

select sum(subject) as countsubject,name from student group by name order by countsubject desc limit 10;

(6)sql查询各科成绩最好的学生扩展阅读：

学生成绩表常用sql

1. 在表中插入符合主键

[sql]

/*成绩表*/

CREATE TABLE SC

(

Sid INT REFERENCES Student(Sid), /*学生学号*/

Cid INT REFERENCES Course(Cid), /*课程编号*/

Score INT NOT NULL, /*课程分数*/

PRIMARY KEY(Sid,Cid) /*将学生学号和课程编号设为复合主键*/
)

2. 查询各科成绩最高分，最低分以及平均分

[sql]

SELECT c.Cname, MAX(s.Score) AS Max, MIN(s.Score) AS Min, AVG(s.Score) AS Average

FROM Course c JOIN SC s ON c.Cid = s.Cid

GROUP BY c.Cname

/*此处应注意，若不按照c.Cname进行分组，SQL语句会报错，c.Cname在SELECT语句中不合法，因为它并未出现在聚合函数中也没有出现在GROUP BY语句中*/

3. 查询平均成绩大于80分的学生姓名以及平均成绩

[sql]

SELECT Sname, AVG(Score) AS Average FROM Student JOIN SC

ON Student.Sid=SC.Sid

GROUP BY Sname

HAVING AVG(Score)>80

/*以聚合函数为条件进行删选只能在HAVING语句中进行，WHERE语句不支持聚合函数*/

4.按总分为学生排名，总分相同名次相同

[sql]

SELECT RANK() OVER (ORDER BY SUM(ss.Score) DESC) AS Rank, s.Sname,

ISNULL(SUM(ss.Score),0)

FROM Student s LEFT JOIN SC ss

ON s.Sid = ss.Sid

GROUP BY s.Sname

ORDER BY SUM(ss.Score) DESC

/*RANK()是SQL Server的一个built-in函数，语法为

RANK() OVER ( [ partition_by_clause ] order_by_clause ).*/

5. 查询总分在100至200之间的学生姓名及总分

[sql]

SELECT s.Sname,SUM(ss.Score) FROM Student s JOIN SC ss ON s.Sid=ss.Sid

GROUP BY s.Sname HAVING SUM(ss.Score) BETWEEN 100 AND 200

7. SQL求各科成绩最高分，显示最高成绩的姓名及成绩

selectSname,scorefromStudent,ScwhereStudent.Sno=Sc.Snoandscore=(selectMAX(score)fromScwhereSc.Sno=Student.Sno)
--或者
selectSname,scorefrom(
selectSname,score,row_number()over(partitionbySc.SnoorderbyscoreDesc)AsRkfromStudent,ScwhereStudent.Sno=Sc.Sno
)Swhererk=1

8. sql sever 2008r2查询各系各科成绩最高分的学生的学号，姓名，系名，课程名称，成绩

select
a.sno学号,a.sname姓名,a.sdept系名,c.cname课程名称版,b.maxgrade成绩权
from
studenta
innerjoin(selectcno,max(sno)sno,max(grade)maxgradefromscgroupbycno)bona.sno=b.sno
innerjoincourseconb.con=c.cno

9. SQL查询单科成绩最高的同学

SELECT child.abc,child.cource,a.name

FROM (select max(b.point) as abc,c.cource from `student` as a join `achievement` as b join `course` as c on a.sex = 1 and b.sid=a.id and b.cid=c.id group by c.cource) as child

join `student` as a join `achievement` as b join `course` as c on a.sex = 1 and b.sid=a.id and b.cid=c.id where child.abc=b.point and child.cource=c.cource

很繁琐,子查询和查询的都是同一个表同一个条件,答案包对专

不要姓名要学属号的话就把名字换一下

原理

子查询出最高分和科目,再用父查询把(同条件下)把最高分和科目配对

10. SQL查询语句： 查询 每科目分数最高的5项(学生姓名，科目，分数)

sql server 2005及以上版本如下，sql server 2000的话得用别的方法了
declare @t table (
sname varchar(30)
,ssubject varchar(30)
,score int
)

insert into @t
select '姓名' 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