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