一道有趣的SQL题目

来源：互联网发布：mac office用户名修改编辑：程序博客网时间：2024/05/17 04:59

--题目：

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

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

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

--实现步骤如下：

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

drop table score_table

create table score_table

(

id int identity (1,1) primary key,

name varchar(20),

score float

)

插入一些测试用的数据

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

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('李四',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

执行结果：

name avgScore

赵六 76.8

田七 76.5

王五 76.0

张三 73.3

李四 65.9