这个sql语句怎么写了?

[xingxingxiangrong]

id fenshu user
1 1 张三
2 2 张三
3 3 李四
1 1 张三
2 2 张三
3 3 李四
1 1 张三
2 2 张三
3 3 李四
1 1 张三
2 2 张三
3 3 李四
1 1 张三
2 2 张三
3 3 李四
1 1 张三
2 2 张三
3 3 李四



我要实现
张三的记录数<10 直接求平均
10<=张三的记录数<=19,去掉最高的1个和最低的1个然后求平均
20<=张三的记录数时>29,去掉最高的2个和最低的2个然后求平均

这个怎么写了

[xingxingxiangrong]

要查询分数和对应的人

[xingxingxiangrong]

最后由高到低排序,谁会了,谢谢了

[xuliabc]

up

[tippointGmail]

use tempdb
go

create table t1(
ID int not null,
fenshu int not null,
[user] varchar(10) not null
)
go

insert into t1
select 1,1,'张三' union all
select 2,2,'张三' union all
select 3,3,'李四' union all
select 1,1,'张三' union all 
select 2,2,'张三' union all 
select 3,3,'李四' union all 
select 1,1,'张三' union all 
select 2,2,'张三' union all
select 3,3,'李四' union all
select 1,1,'张三' union all
select 2,2,'张三' union all
select 3,3,'李四' union all
select 1,1,'张三' union all
select 2,2,'张三' union all 
select 3,3,'李四' union all 
select 1,1,'张三' union all 
select 2,2,'张三' union all 
select 3,3,'李四' 
go

select * from t1 order by [user]


select 
a.[user],
avgscore=(case when n.num<10 then sum(fenshu)/n.num 
          when n.num>=10 and n.num<=19 then (sum(fenshu)-(select top 1 fenshu from t1 where [user]=a.[user] order by fenshu desc)-(select top 1 fenshu from t1 where [user]=a.[user] order by fenshu ))*1.0/(n.num-2) 
when n.num>=20 and n.num<=29 then (sum(fenshu)-(select top 2 fenshu from t1 where [user]=a.[user] order by fenshu desc)-(select top 2 fenshu from t1 where [user]=a.[user] order by fenshu ))*1.0/(n.num-4) 
end)
 from t1 a
inner join (select [user],count(*) num from t1 group by [user]) n on a.[user]=n.[user]
group by a.[user],n.num

[tippointGmail]

结果
user avgscore
李四 3.000000000000
张三 1.500000000000

[imfzp]

declare @table table(id int,fenshu real,[user] nvarchar(20))
insert @table
			select 1  ,    1  ,  N'张三'
union all	select 2,      2 ,    N'张三'
union all	select 3  ,    3 ,   N'李四'
union all	select 1   ,   1,    N'张三'
union all	select 2   ,   2 ,   N'张三'
union all	select  3  ,    3  , N'李四'
union all	select  1  ,    1 ,   N'张三'
union all	select 2   ,   2 ,   N'张三'
union all	select 3 ,     3 ,   N'李四'
union all	select 1 ,     1  ,  N'张三'
union all	select 2  ,    2  ,  N'张三'
union all	select 3  ,    3  ,  N'李四'
union all	select 1  ,    1 ,   N'张三'
union all	select 2  ,    2  ,  N'张三'
union all	select 3  ,    3 ,   N'李四'
union all	select 1 ,     1  ,  N'张三'
union all	select 2  ,    2 ,   N'张三'
union all	select 3   ,   3  ,  N'李四'


select 
 a.[user] 
, B.COUN,
(case when b.coun<10 
		then sum(fenshu)/b.coun
      when b.coun>=10 and b.coun<20 
		then ((sum(fenshu)-(select max(fenshu)+min(fenshu) from @table z where z.[user]=a.[user])))/(b.coun-2)
      when b.coun>=20 
        then (sum(fenshu)
				- (select sum(fenshu) from 
					(select top 2 fenshu from @table z where z.[user]=a.[user] 
					union all
					select top 2 fenshu from @table z where z.[user]=a.[user]) y
				  ))/(b.coun-4)
END
)
from @table a
,(select [user],count(*) coun from @table  group by [user] ) b
where a.[user]=b.[user]
group by a.[user],B.COUN

结果
张三 12 1.5
李四 6 3

[xingxingxiangrong]

谢谢你们了