列出每个班每个科目的最高分及学生姓名

2014-10-25 22:35:28  访问(2560) 赞(0) 踩(0)

• SQL语句
• SQL执行结果

• 
  
  select 
  c.TheName as '班级',
  co.TheName as '科目',
  s.TheName as '学生',
  r2.maxScore as '最高分'
  from
  UTB_EXAM_CLASS c,
  UTB_EXAM_COURSE co,
  UTB_EXAM_SCORE sc,
  UTB_EXAM_STUDENT s,
  (
  	select 
  	r.CourseId,r.ClassId,max(r.Score) as maxScore
  	from 
  	(
  		select
  		sc.*,s.ClassId
  		from
  		UTB_EXAM_SCORE sc,
  		UTB_EXAM_STUDENT s
  		where
  		sc.StudentId = s.ID
  	)r
  	group by
  	r.CourseId,r.ClassId
  )r2
  where
  c.ID = r2.ClassId
  and co.ID = r2.CourseId
  and sc.StudentId = s.ID
  and sc.CourseId = r2.CourseId
  and sc.Score = r2.maxScore
  and s.ClassId = r2.ClassId
  order by c.ID,co.ID,s.TheName
  
  

标签：列出每个班每个科目的最高分及学生姓名 

上一条：

下一条：

相关评论

发表评论

类型：	留言 建议 疑问
内容：
	(Alt+Enter)