SQL求科目最高学生信息

SELECT 课程表kc.课程名, 成绩表cj.成绩, 学生表xs.学号, 系名表xm.系名;
FROM ;
student!课程表kc ;
INNER JOIN student!成绩表cj ;
ON 课程表kc.课程号 = 成绩表cj.课程号 ;
INNER JOIN student!学生表xs ;
ON 学生表xs.学号 = 成绩表cj.学号 ;
INNER JOIN student!系名表xm ;
ON 系名表xm.系号 = 学生表xs.系号;
WHERE 成绩表cj.成绩 IN (SELECT MAX(成绩) as 最 from 成绩表cj GROUP BY 成绩表cj.课程号)

为什么我求出来的每个科目的成绩有多条，哪里出问题了

本来我想仔细看看，然后回答的，但是看到这满眼的中文，甚至表名字段名都是中文的时候，我实在没法看。。。什么习惯这是

表结构都没有 让别人凭空想象？

不管你的对与错

 WHERE 成绩表cj.成绩 IN (SELECT MAX(成绩) as 最 from 成绩表cj GROUP BY 成绩表cj.课程号) limit 1

http://www.cnblogs.com/lsgcoder101/p/6011059.html

学生各门课程成绩统计SQL语句大全
创建表
create table stuscore(
name varchar(50) ,
subject varchar(50),
score int ,
stuid int
)
插入数据

insert into dbo.stuscore values ('张三','数学',89,1)；
insert into dbo.stuscore values ('张三','语文',80,1)；
insert into dbo.stuscore values ('张三','英语',70,1)；
insert into dbo.stuscore values ('李四','数学',90,2)；
insert into dbo.stuscore values ('李四','语文',70,2)；
insert into dbo.stuscore values ('李四','英语',80,2)；
查询结果显示，如下截图：
问题：
1.计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)
select name,SUM(score) as allscore from dbo.stuscore
group by name
order by allscore;
View Code
2.计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)
select stuid,name,SUM(score) as allscore from dbo.stuscore
group by name,stuid
order by allscore;
View Code
3.计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)
select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,
(select stuid,max(score) as maxscore from stuscore group by stuid) t2
where t1.stuid=t2.stuid and t1.score=t2.maxscore;
View Code
4.计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）
select stuid,name,AVG(score) avgscore from dbo.stuscore
group by stuid,name;
View Code
5.列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,(
select subject,MAX(score) as maxscore from stuscore group by subject)t2
where t1.subject = t2.subject and t1.score = t2.maxscore;
View Code
6.列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)

select t1.* from stuscore t1 where t1.stuid in (
select top 2 stuid from stuscore where subject = t1.subject order by score desc)
order by t1.subject;
View Code
7.统计如下：

学号
姓名
语文
数学
英语
总分
平均分

select stuid 学号,name 姓名,sum(case when subject='语文' then score else 0 end )as 语文,
sum(case when subject='数学' then score else 0 end )as 数学,
sum(case when subject='英语' then score else 0 end )as 英语,
SUM(score)总分,avg(score)平均分 from stuscore
group by stuid,name order by 总分;
View Code
8．列出各门课程的平均成绩（要求显示字段：课程，平均成绩）

select subject,AVG(score)平均成绩 from stuscore
group by subject;
select stuid,name,AVG(score) avgscore from dbo.stuscore
group by stuid,name;
View Code

9．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

select stuid,name,score,
(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from stuscore t2
where subject='数学' order by score desc;
--注释：排序，比较大小，比较的次数+1 = 排名
View Code

10．列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）
View Code
select t3.* from (
select top 2 t2.* from (
select top 3 stuid,name,subject,score from stuscore where
subject = '数学' order by score desc) t2 order by t2.score) t3
order by t3.score desc;

select t3.* from (
select top 100 percent stuid,name,subject,score,
(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from
stuscore t2 where subject='数学' order by t2.score desc) t3
where t3.名次 between 2 and 3 order by t3.score desc;
select t3.* from (
select stuid,name,subject,score,
(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from
stuscore t2 where subject='数学') t3
where t3.名次 between 2 and 3 order by t3.score desc;
后面两个方法的不同之处可以参见：http://blog.csdn.net/wrm_nancy/article/details/17170115
11．求出李四的数学成绩的排名
View Code
declare @tmp table(pm int,name varchar(50),score int,stuid int)insert into @tmp
select null,name,score,stuid from stuscore where subject='数学'
order by score desc declare @id int set @id=0;
update @tmp set @id=@id+1,pm=@id select * from @tmp where name='李四'
select stuid,name,subject,score,(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次
from stuscore t2 where subject='数学' and name = '李四' order by score desc;
12．统计如下：

课程
不及格（0-59）个
良（60-80）个
优（81-100）个

select subject 科目,sum(case when score between 0 and 59 then 1 else 0 end) as 不及格,
sum(case when score between 60 and 80 then 1 else 0 end) as 良,
sum(case when score between 81 and 100 then 1 else 0 end) as 优秀 from stuscore
group by subject;
View Code
13．统计如下：
数学: 张三(50分),李四(90分),王五(90分),赵六(76分)
declare @s nvarchar(1000)
set @s=''
select @s =@s+','+name+'('+convert(nvarchar(10),score)+'分)' from
stuscore where subject='数学'
set @s=stuff(@s,1,1,' ')print '数学:'+@s

稍微做了下，仅供参考，考虑到你每个科目成绩有多条，说明有多个人有同一科目的成绩：
1.建模

2.第一步

3.第二步

4.第三步

5.第四步

效果无误，不存在一个科目多条成绩，
和楼主过程的区别在于ON 后面的全都是以grade表中的数据进行链接，楼主可检查是否这个地方出现了问题