select avg(cast(score as int)) from ( select top 5 score from tablename order by score desc )aa
select avg(col) from ( select top 5 cast(col as decimal(18,2)) col from tb order by cast(col as decimal(18,2)) desc ) t
select score from (select top 5 convert(int,score) as score from tb order by convert(int,score) desc)t
确实有问题,order by 后面这个score需要转换一下select avg(cast(score as int)) from ( select top 5 score from tablename order by score+0 desc )aa
select avg(成绩) from ( select top 5 convert(decimal(18,2),成绩) 成绩 from tb order by 1 desc ) a
select avg(t.qty_score) as score from (select top 5 qty_score from tab where isnumeric(qty_score) = 1 order by qty_score desc) t
select avg(col) from ( select top 5 cast(col as decimal(4,2)) col from tb order by cast(col as decimal(4,2)) desc ) t
用排序后在用 TOP 然后出来的就是就是你指定要出来多少个的数目
use students; go if object_id('score')is not null drop table score create table score (score nchar(4)) insert into score select 100 union all select 100 union all select 90 union all select 90 union all select 95 union all select 24 union all select 88 union all select 49select avg(score1)from (select top 5 cast(score as int)as score1 from score order by score1 desc)b /*----------- 95(1 行受影响) */
select top 5 avg(convert(decimal(4,2),score)) from tableName order by score desc
随便问下,谁设计的数据表,为什么记录成绩的字段是nchar类型?
declare @scoretable table (StudentId int,score nchar(5)) insert into @scoretable select 1,100 union all select 2,80 union all select 3,96 union all select 4,92 union all select 5,99 union all select 6,91 union all select 7,99 union all select 8,99 union all select 9,96 union all select 10,96--不考虑并列 select avg(score) from ( select top 5 score=cast(score as decimal(18,2)) from @scoretable order by cast(score as decimal(18,2)) desc ) a /* 98.600000 */--考虑并列 select score=avg(cast(score as decimal(18,2))) from @scoretable where cast(score as decimal(18,2))>=( select min(score) from ( select top 5 score=cast(score as decimal(18,2)) from @scoretable order by cast(score as decimal(18,2)) desc) a)/* 97.857142 */
if object_id('score')is not null drop table score create table score (score nchar(4)) insert into score select 100 union all select 100 union all select 90 union all select 90 union all select 95 union all select 24 union all select 88 union all select 49select avg(score1)from (select top 5 cast(score as int)as score1 from score order by score1 desc)b
(
select top 5 score from tablename order by score desc
)aa
(
select top 5 cast(col as decimal(18,2)) col from tb order by cast(col as decimal(18,2)) desc
) t
score
from
(select top 5 convert(int,score) as score from tb order by convert(int,score) desc)t
(
select top 5 score from tablename order by score+0 desc
)aa
select avg(成绩) from (
select top 5 convert(decimal(18,2),成绩) 成绩 from tb
order by 1 desc
) a
select avg(t.qty_score) as score
from (select top 5 qty_score from tab
where isnumeric(qty_score) = 1
order by qty_score desc) t
select avg(col) from
(
select top 5 cast(col as decimal(4,2)) col from tb order by cast(col as decimal(4,2)) desc
) t
go
if object_id('score')is not null
drop table score
create table score
(score nchar(4))
insert into score
select 100 union all
select 100 union all
select 90 union all
select 90 union all
select 95 union all
select 24 union all
select 88 union all
select 49select avg(score1)from
(select top 5 cast(score as int)as score1 from score order by score1 desc)b
/*-----------
95(1 行受影响)
*/
declare @scoretable table (StudentId int,score nchar(5))
insert into @scoretable
select 1,100 union all
select 2,80 union all
select 3,96 union all
select 4,92 union all
select 5,99 union all
select 6,91 union all
select 7,99 union all
select 8,99 union all
select 9,96 union all
select 10,96--不考虑并列
select avg(score) from
(
select top 5 score=cast(score as decimal(18,2)) from @scoretable
order by cast(score as decimal(18,2)) desc
) a
/*
98.600000
*/--考虑并列
select score=avg(cast(score as decimal(18,2))) from @scoretable
where cast(score as decimal(18,2))>=(
select min(score) from (
select top 5 score=cast(score as decimal(18,2))
from @scoretable order by cast(score as decimal(18,2)) desc) a)/*
97.857142
*/
drop table score
create table score
(score nchar(4))
insert into score
select 100 union all
select 100 union all
select 90 union all
select 90 union all
select 95 union all
select 24 union all
select 88 union all
select 49select avg(score1)from
(select top 5 cast(score as int)as score1 from score order by score1 desc)b