一道有趣的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