一道有趣的SQL题目

 

--题目：

--已知：现有个学生，每个学生有十门课程的成绩，分数类型为float型的，

--要求：去掉最高和最低分后求平均分，然后排名次。此例不考虑其中有缺考的请考的情况。

--查询结果显示两列，姓名和平均分，并且分数只能保留一位小数。

--实现步骤如下：

if exists (select * from sysobjects where name='score_table')

    drop table score_table

go

 

create table score_table

(

    id int identity (1,1) primary key,

    name varchar(20),

    score float

)

go

插入一些测试用的数据

insert into score_table values('张三',56)

insert into score_table values('张三',89)

insert into score_table values('张三',89)

insert into score_table values('张三',78)

insert into score_table values('张三',65)

insert into score_table values('张三',55)

insert into score_table values('张三',83)

insert into score_table values('张三',82)

insert into score_table values('张三',73)

insert into score_table values('张三',67)

insert into score_table values('张三',67)

 

insert into score_table values('李四',88)

insert into score_table values('李四',45)

insert into score_table values('李四',67)

insert into score_table values('李四',87)

insert into score_table values('李四',46)

insert into score_table values('李四',83)

insert into score_table values('李四',48)

insert into score_table values('李四',65)

insert into score_table values('李四',83)

insert into score_table values('李四',48)

 

insert into score_table values('王五',76)

insert into score_table values('王五',66)

insert into score_table values('王五',79)

insert into score_table values('王五',89)

insert into score_table values('王五',67)

insert into score_table values('王五',86)

insert into score_table values('王五',66)

insert into score_table values('王五',49)

insert into score_table values('王五',79)

insert into score_table values('王五',97)

 

insert into score_table values('赵六',97)

insert into score_table values('赵六',76)

insert into score_table values('赵六',56)

insert into score_table values('赵六',99)

insert into score_table values('赵六',56)

insert into score_table values('赵六',77)

insert into score_table values('赵六',74)

insert into score_table values('赵六',65)

insert into score_table values('赵六',93)

insert into score_table values('赵六',76)

 

insert into score_table values('田七',34)

insert into score_table values('田七',56)

insert into score_table values('田七',98)

insert into score_table values('田七',86)

insert into score_table values('田七',68)

insert into score_table values('田七',74)

insert into score_table values('田七',86)

insert into score_table values('田七',68)

insert into score_table values('田七',96)

insert into score_table values('田七',78)

 

select * from score_table

 

--查询语句：

select name, convert(decimal(3,1),((sum(score)-min(score)-max(score))/(count(*)-2))) as avgScore from score_table group by name order by avgScore desc

go

执行结果：

name    avgScore

赵六     76.8

田七     76.5

王五     76.0

张三     73.3

李四     65.9