求SQL语句

Question

6.下面的表用来记载体育比赛中运动员信息、裁判员信息以及运动员比赛时裁判给运动员的表现所打的分数：
    create table player(pid int primary key, --运动员编号
                        pName nvarchar(20), --运动员姓名
                        sex bit) --运动员性别    create table umpire(uid int primary key, --裁判编号
                        uName nvarchar(20), --裁判姓名
                        nation nvarchar(20)) --裁判国籍    create table score(sid  int identity(1,1) primary key,
                       pid int references player(pid), --运动员编号
                       uid int references umpire(uid), --裁判员编号
                       score int) --成绩 请编写一个函数finalScore，实现计算运动员的最终得分，最终得分的计算方法是：去掉一个最高分、去掉一个最低分、剩余的分数求平均。并利用该函数建立一个视图来显示各运动员的最终成绩,显示的数据为运动员编号、姓名、最终成绩。

Answer 1

Create function fn_score(
@pid int
)returns int
begin
declare @i int
select @i=avg(score) from score as a 
where ID not in(select top 1 ID from score where pid=a.pid order by score desc )
and ID not in(select top 1 ID from score where pid=a.pid order by score asc )
return @i
end

Answer 2

用ID判断， score 最低、最高有两个裁判以上打分相同时只减一条记录

Answer 3

--去掉最高分 最低分求平均分
select pid,avg(score) as score from score t where sid not in(select top 1 sid from score where pid=t.pid order by score)
and sid not in(select top 1 sid from score where pid=t.pid order by score desc)
group by pid
剩下的和表a 做个连接查询。

Answer 4

我用  select dbo.fn_score('1')  作为运行语句，那么如何让它的结果保留两位小数呢

Answer 5

改一下函数名finalScore
Create function finalScore(
@pid int
)returns decimal(10,2)
begin
declare @i int
select @i=cast(avg(score*1.0) as decimal(10,2)) from score as a 
where ID not in(select top 1 ID from score where pid=a.pid order by score desc )
    and ID not in(select top 1 ID from score where pid=a.pid order by score asc )
return @i
end

Answer 6

avg(score) 
替换成 
cast(avg(score*1.0)as decimal(6,2))

Answer 7

if object_id('score','U') is not null
   drop table score
go
if object_id('player','U') is not null
   drop table player
go
  create table player(pid int primary key, --运动员编号
  pName nvarchar(20), --运动员姓名
  sex bit) --运动员性别
go
insert into player (pid,pName,sex)
select 1,'张三','true' union all
select 2,'李四','false'
go
if object_id('umpire','U') is not null
   drop table umpire
go
create table umpire(uid int primary key, --裁判编号
  uName nvarchar(20), --裁判姓名
  nation nvarchar(20)) --裁判国籍
go
insert into umpire (uid,uName,nation)
select 1,'小三','日本' union all
select 2,'小四','法国'
go
if object_id('score','U') is not null
   drop table score
go
create table score(sid int identity(1,1) primary key,
  pid int references player(pid), --运动员编号
  uid int references umpire(uid), --裁判员编号
  score int) --成绩
go
insert into score (pid,uid,score)
select 1,1,90 union all
select 1,1,80 union all
select 1,1,70 union all
select 1,1,60 union all
select 1,1,50 union all
select 2,2,91 union all
select 2,2,81 union all
select 2,2,71 union all
select 2,2,61 union all
select 2,2,51
go
with cte as
(
select t1.* from 
(
 select sid,pid,uid,score from (select *,row=row_number() over(partition by pid order by score) from score) a where row >1
) t1 inner join 
(
 select sid,pid,uid,score from (select *,row=row_number() over(partition by pid order by score desc) from score) a where row>1
) t2 
on t1.sid=t2.sid
)
select cte.pid,pName,最终成绩=avg(score) from cte inner join player on cte.pid=player.pid group by cte.pid,pName
/*
pid         pName                最终成绩
----------- -------------------- -----------
1           张三                   70
2           李四                   71(2 行受影响)
*/

Answer 8

改改
alter function finalScore(
@pid int
)returns decimal(10,2)
begin
declare @i decimal(10,2)
select @i=cast(sum(score*1.0)/sum(1) as decimal(10,2)) from score as a  
where sid not in(select top 1 sid from score where pid=a.pid order by score desc )
    and sid not in(select top 1 sid from score where pid=a.pid order by score asc )
    and pid=@pid
return @i
end
go

Answer 9

sql 2000表示对row_number()很无奈啊.

Answer 10

以15楼和以下方法测测
或用
create table score(sid int identity(1,1) primary key,
  pid int ,  --运动员编号
  uid int ,  --裁判员编号
  score int)  --成绩
go
insert into score select 1,1,6
insert into score select 1,2,3
insert into score select 1,3,3
insert into score select 1,4,4
insert into score select 1,5,10go
alter function finalScore(
@pid int
)returns decimal(10,2)
begin
declare @i decimal(10,2)
select @i=cast(avg(score*1.0) as decimal(10,2)) from score as a  
where sid not in(select top 1 sid from score where pid=a.pid order by score desc )
    and sid not in(select top 1 sid from score where pid=a.pid order by score asc )
    and pid=@pid
return @i
end
go
select dbo.finalScore(1)
--等同于以下
select 13.0/3