简单查询语句，高手请进，要求一句SQL。得到答案后，散分50分(100分)

SQL Table<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;考生 &nbsp; &nbsp; &nbsp; &nbsp; 类别 &nbsp; &nbsp; &nbsp; SCORE<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;赵一 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;552<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;钱二 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;578<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;孙三 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;579<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;李四 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;502<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;周五 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;527<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;武六 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;532<br>要求用一句SQL查询，并运行SQL SERVER 2000命令<br>查询结果：<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;记录 &nbsp; &nbsp; &nbsp;类别 &nbsp; &nbsp; &nbsp; SCORE<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;552<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;575<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;576<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;502<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;527<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 26 &nbsp; // &nbsp;相邻的记录之差，最大的一个数据<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;16 &nbsp; // 相邻的记录之差的累加和除以记录数[]

立等答案，急用。<br>可怜愁煞人！

/*<br>create table score(<br>name varchar(20),<br>class varchar(20),<br>score float<br>)<br><br>insert into score values('赵一','文科',552)<br>insert into score values('钱二','文科',578)<br>insert into score values('孙三','理科',579)<br>insert into score values('李四','理科',502)<br>insert into score values('周五','文科',527)<br>insert into score values('武六','理科',532)<br>*/<br><br>select identity(int,1,1) rowid,name,class,score into #temp from score<br><br>declare @s float,@e float,@total float,@temp float,@i int<br>set @s=0<br>set @e=0<br>set @i=0<br>set @temp=0<br>set @total=0<br>declare diff cursor for select score from score<br>open diff<br>fetch next from diff into @s --print @s<br>while @@fetch_status=0<br>begin<br>&nbsp; --print @e<br>&nbsp; set @i=@i+1<br>&nbsp; if @e&gt;0 <br>&nbsp; begin<br>&nbsp; &nbsp; --print convert(varchar,@e) + ' ' +convert(varchar,@s)<br>&nbsp; &nbsp; if (@e-@s)&gt;@temp<br>&nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; set @temp = @e-@s<br>&nbsp; &nbsp; end<br>&nbsp; &nbsp; --print @e-@s<br>&nbsp; &nbsp; set @total=@total+(@e-@s)<br>&nbsp; &nbsp; set @s=@e <br>&nbsp; end<br>&nbsp; fetch next from diff into @e<br>end<br>close diff<br>deallocate diff<br>insert into #temp (class,score) values('最大差值：',@temp)<br>insert into #temp (class,score) values('平均：',@total/@i)<br><br>select * from #temp order by rowid<br>drop table #temp

/*<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 考生 &nbsp; &nbsp; &nbsp; &nbsp; 类别 &nbsp; &nbsp; &nbsp; SCORE<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;赵一 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;552<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;钱二 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;578<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;孙三 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;579<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;李四 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;502<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;周五 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;527<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;武六 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;532<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;记录 &nbsp; &nbsp; &nbsp;类别 &nbsp; &nbsp; &nbsp; SCORE<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;552<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;575<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;576<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;502<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;527<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 26 &nbsp; // &nbsp;相邻的记录之差，最大的一个数据<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;16 &nbsp; // 相邻的记录之差的累加和除以记录数[] &nbsp;<br><br>*/<br><br>create table tb([考生] &nbsp;varchar(10) &nbsp; &nbsp;, &nbsp; &nbsp; [类别] varchar(10), &nbsp; &nbsp; &nbsp; SCORE int)<br>insert into tb values('赵一','文科',552)<br>insert into tb values('钱二','文科',578)<br>insert into tb values('孙三','理科',579)<br>insert into tb values('李四','理科',502)<br>insert into tb values('周五','文科',527)<br>insert into tb values('武六','理科',532)<br><br>select max(v) ,convert(numeric(18,2),sum(v)) / count(*) from <br>(<br>select v=SCORE-(select max(tb2.SCORE) &nbsp;from tb as tb2 where tb2.SCORE &lt;tb.SCORE) from tb ) as aa<br>where v is not null<br><br>drop table tb

andrew57: &nbsp; &nbsp; &nbsp; &nbsp;平均值为 &nbsp;-3浮点数，不合要求。6条记录，差值查询后应为5条记录<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; （23+1+26+25+5）/（6-1）=16<br>hfghfghfg &nbsp; &nbsp; &nbsp; &nbsp;平均值为 &nbsp; 15.4 &nbsp; 错了。<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;如果得出答案：分数大部分给一方<br>TSQL Table<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;考生 &nbsp; &nbsp; &nbsp; &nbsp; 类别 &nbsp; &nbsp; &nbsp; SCORE<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;赵一 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;552<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;钱二 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;575<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;孙三 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;576<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;李四 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;502<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;周五 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;527<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;武六 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;532<br>要求用一句SQL查询，并运行SQL SERVER 2000命令<br>查询结果：<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;记录 &nbsp; &nbsp; &nbsp;类别 &nbsp; &nbsp; &nbsp; SCORE<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;23 &nbsp; &nbsp; <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;26 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;25<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp; 5 <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 26 &nbsp; // &nbsp;相邻的记录之差，最大的一个数据<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;16 &nbsp; // 相邻的记录之差的累加和除以记录数

andrew57,请将答案修正，查询简单些。给你80分。否则全给hfghfghfg了。

钱二 &nbsp; &nbsp; &nbsp; &nbsp; 文科 &nbsp; &nbsp; &nbsp; &nbsp;575<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;孙三 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;576<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;李四 &nbsp; &nbsp; &nbsp; &nbsp; 理科 &nbsp; &nbsp; &nbsp; &nbsp;502<br><br>26???

接受答案了.