sql求各门课程去掉一个最高分和最低分后的平均分

sql求各门课程去掉一个最高分和最低分后的平均分

--学生表
create table s
(
sno int ,
sname nvarchar(16),
age int,
sex varchar(2)
)

--成绩表
create table sc
(
sno int,
cno int,
grade int
)

--课程表
create table c
(
cno int,
cname nvarchar(25),
teacher nvarchar(25)
)
--插入学生
insert into s values(6,'宋八',18,'男')
insert into s values(5,'王七',18,'男')
insert into s values(4,'赵六',18,'女')

--插入 课程
insert into c values(1,'语文','张老师')
insert into c values(2,'数学','王老师')
insert into c values(3,'外语','李老师')

select * from s

--插入成绩
insert into sc values(1,1,20)
insert into sc values(1,2,30)
insert into sc values(1,3,40)

insert into sc values(2,1,70)
insert into sc values(2,2,60)
insert into sc values(2,3,50)

insert into sc values(3,1,50)
insert into sc values(3,2,25)
insert into sc values(3,3,30)

insert into sc values(4,1,20)
insert into sc values(4,2,30)
insert into sc values(4,3,40)

insert into sc values(5,1,28)
insert into sc values(5,2,30)
insert into sc values(5,3,48)

insert into sc values(6,1,20)
insert into sc values(6,2,30)
insert into sc values(6,3,40)

--该函数获得每门课程除去最低分和最高分后的平均成绩
--传入参数为 课程编号
create function cg(@cid int) returns table
as
return (select cs.cname 课程名称,avg(newsc.grade) as 平均分 from sc as newsc,c as cs where grade not in ((select max(grade) from sc where cno = @cid), (select min(grade) from sc where cno = @cid))
and newsc.cno = @cid and cs.cno = newsc.cno group by cs.cname)

--查询结果
select * from dbo.cg(1) union select * from dbo.cg(2) union select * from dbo.cg(3)

平均分用 avg（）

学生表：S SNO(学生编号) SNAME(姓名) AGE(年龄) SEX(性别)
关系表：SC SNO CNO GRADE(评分)
课程表：C CNO(课程编号) CNAME(课程名称) TEACHER(任课教师)

求各门课程去掉一个最高分和最低分后的平均分,

我建表测试过了，没有问题：

SELECt tt1.cno AS 课程编号,AVG(grade) AS 平均分
FROM
(
SELECt C.cno,grade
FROM S
INNER JOIN SC
ON S.sno = SC.sno
INNER JOIN C
ON SC.cno = C.cno
) tt1
INNER JOIN
(
SELECt cno,max_g,min_g
FROM
(
SELECt C.cno,MAX(grade) AS max_g
FROM S
INNER JOIN SC
ON S.sno = SC.sno
INNER JOIN C
ON SC.cno = C.cno
GROUP BY C.cno
) t1
INNER JOIN
(
SELECt C.cno,MIN(grade) AS min_g
FROM S
INNER JOIN SC
ON S.sno = SC.sno
INNER JOIN C
ON SC.cno = C.cno
GROUP BY C.cno
) t2
USING (cno)
) tt2
ON tt1.cno = tt2.cno
AND
tt1.grade NOT IN (tt2.max_g,tt2.min_g)
GROUP BY tt1.cno;