一道面试题（有意思）

Question

表T(Id,StudentsId,StudentName,CourseId,CourseName,Grade,TeacherId,TeacherName)
表示如下关系：
学生ID，学生姓名，课程ID，课程名，成绩，教师ID，教师名》用SQL脚本完成如下问题：
1、如果表T还有一个字段ID，数据类型为自动增长类型（主键），而且表T中出ID字段外，请删除其他字段完全相同的重复多余字段。
2、列出各科成绩最高和最低的相关记录，（就是各课成绩最高和最低的分）；课程名，学生ID，学生姓名，教师姓名，教师ID，成绩。
3、列出4课程平均成绩和及格率百分数，课程名，课程ID，平均成绩，百分率。
4、列出各科成绩前3名记录；课程ID，课程名，学生ID，学生名，成绩。各位大侠们，请出招吧……

Answer 1

1、如果表T还有一个字段ID，数据类型为自动增长类型（主键），而且表T中出ID字段外，请删除其他字段完全相同的重复多余字段说真的,第一句都没读懂

Answer 2

1.
delete t from t m where id not in (select min(id) from t where StudentsId=m.StudentsId and StudentName=m.StudentName and CourseId=m.CourseId and CourseName=m.CourseName and Grade=m.Grade and TeacherId=m.TeacherId and TeacherName=m.TeacherName)
delete t from t m where id not in (select max(id) from t where StudentsId=m.StudentsId and StudentName=m.StudentName and CourseId=m.CourseId and CourseName=m.CourseName and Grade=m.Grade and TeacherId=m.TeacherId and TeacherName=m.TeacherName)2.
select m.* from t m where Grade = (select max(Grade) from t where CourseId = m.CourseId)
union all
select m.* from t m where Grade = (select min(Grade) from t where CourseId = m.CourseId)3.
select CourseId,CourseName,avg(Grade*1.0) 平均成绩 ,
       (select count(1) from t where CourseId = m.CourseId and Grade>=60)*100.0 / (select count(1) from t where CourseId = m.CourseId) 及格率百分数
from t
group by CourseId,CourseName4.
select m.* from t m where Grade in (select top 3 Grade from t where CourseId = m.CourseId order by Grade desc) order by CourseId , Grade desc

Answer 3

--1、如果表T还有一个字段ID，数据类型为自动增长类型（主键），而且表T中出ID字段外，请删除其他字段完全相同的重复多余字段。 
delete Tp
from t tp
where id<>
(select MAX(id) from t 
group by StudentsId,StudentName,CourseId,CourseName,Grade,TeacherId,TeacherName)
--2列出各科成绩最高和最低的相关记录，（就是各课成绩最高和最低的分）；课程名，学生ID，学生姓名，教师姓名，教师ID，成绩
select tp.* from t tp where Grade = (select max(Grade) from t where CourseId = tp.CourseId)
or Grade = (select min(Grade) from t where CourseId = tp.CourseId)
--3列出4课程平均成绩和及格率百分数，课程名，课程ID，平均成绩，百分率。
select 
CourseId,CourseName,
平均成绩=AVG(1.*Grade),
百分率=(select COUNT(*) from t where k.CourseId=CourseId)/COUNT(*)
from t k 
group by CourseId,CourseName
--4列出各科成绩前3名记录；课程ID，课程名，学生ID，学生名，成绩。
select *
from (select CourseId,CourseName from t) k cross apply
(select top 3 grade,StudentsId,StudentName from t where k.courseid=courseid order by grade desc ) p