--> 测试数据: #A if object_id('tempdb.dbo.#A') is not null drop table #A go create table #A (ID int,UserID int,Score numeric(2,1),Judge varchar(1)) insert into #A select 1,23,1.5,'A' union all select 2,23,2.1,'B' union all select 3,23,0.9,'C' union all select 4,23,2.5,'D' union all select 5,23,1.1,'E' union all select 6,30,1.4,'A' union all select 7,30,0.5,'B' union all select 8,30,2.5,'C' union all select 9,30,1.7,'D' union all select 10,30,1.0,'E'select A.USERID,SUM(A.Score)/B.NUM from #A A,(SELECT USERID,COUNT(1) AS NUM FROM #A GROUP BY USERID) B WHERE A.USERID=B.USERID AND A.Score<>(SELECT MAX(Score) FROM #A WHERE UserID=A.UserID) AND A.Score<>(SELECT MIN(Score) FROM #A WHERE UserID=A.UserID) GROUP BY A.USERID,B.NUM/* (所影响的行数为 10 行)USERID ----------- ---------------------------------------- 23 .940000 30 .820000(所影响的行数为 2 行)
1楼好复杂,而且人家如果不是那5个数,你还得改代码啊? insert into b (userid,avescore) select a.userid,(a.sum_score-b.max_score-c.min_score)/a.ids as avescore from (select userid,sum(score) as sum_score,count(*) as ids from test group by id) a,(select userid,max(score) as max_score from test group by id) b,(select userid,min(score) as min_score from test group by id) c where a.userid=b.userid and a.userid=c.userid
上面SQL代码写错了一点,应该是group by userid,不是group by id。我建的测试表里叫id,贴过来时忘了改了。
稍微改一下 select A.UserID,SUM(A.Score)/B.NUM from ScoreAvg A,(SELECT UserID,COUNT(1) AS NUM FROM ScoreAvg GROUP BY UserID) B WHERE A.UserID=B.UserID AND A.ID<>(SELECT TOP 1 ID FROM ScoreAvg WHERE UserID=A.UserID ORDER BY Score DESC) AND A.ID<>(SELECT TOP 1 ID FROM ScoreAvg WHERE UserID=A.UserID ORDER BY Score) GROUP BY A.UserID,B.NUM
select userid, (sum(score) - max(score) - min(score))/count(*) as avescore from #A group by userid
if object_id('tempdb.dbo.#A') is not null drop table #A go create table #A (ID int,UserID int,Score numeric(2,1),Judge varchar(1)) insert into #A select 1,23,1.5,'A' union all select 2,23,2.1,'B' union all select 3,23,0.9,'C' union all select 4,23,2.5,'D' union all select 5,23,1.1,'E' union all select 6,30,1.4,'A' union all select 7,30,0.5,'B' union all select 8,30,2.5,'C' union all select 9,30,1.7,'D' union all select 10,30,1.0,'E'select userid, (sum(score) - max(score) - min(score))/count(*) as avescore from #A group by userid
可以,但是要改一下: select userid, (sum(score) - max(score) - min(score))/(count(*)-2) as avescore from #A group by userid 就可以了 nihaoydh的思路不错!!!
受教了,看来我的SQL才刚入门
select userid, (sum(score) - max(score) - min(score))/count(*) as avescore from #A group by userid
if object_id('tempdb.dbo.#A') is not null drop table #A
go
create table #A (ID int,UserID int,Score numeric(2,1),Judge varchar(1))
insert into #A
select 1,23,1.5,'A' union all
select 2,23,2.1,'B' union all
select 3,23,0.9,'C' union all
select 4,23,2.5,'D' union all
select 5,23,1.1,'E' union all
select 6,30,1.4,'A' union all
select 7,30,0.5,'B' union all
select 8,30,2.5,'C' union all
select 9,30,1.7,'D' union all
select 10,30,1.0,'E'select A.USERID,SUM(A.Score)/B.NUM
from
#A A,(SELECT USERID,COUNT(1) AS NUM FROM #A GROUP BY USERID) B
WHERE A.USERID=B.USERID
AND A.Score<>(SELECT MAX(Score) FROM #A WHERE UserID=A.UserID)
AND A.Score<>(SELECT MIN(Score) FROM #A WHERE UserID=A.UserID)
GROUP BY A.USERID,B.NUM/*
(所影响的行数为 10 行)USERID
----------- ----------------------------------------
23 .940000
30 .820000(所影响的行数为 2 行)
insert into b (userid,avescore) select a.userid,(a.sum_score-b.max_score-c.min_score)/a.ids as avescore from (select userid,sum(score) as sum_score,count(*) as ids from test group by id) a,(select userid,max(score) as max_score from test group by id) b,(select userid,min(score) as min_score from test group by id) c where a.userid=b.userid and a.userid=c.userid
select A.UserID,SUM(A.Score)/B.NUM
from
ScoreAvg A,(SELECT UserID,COUNT(1) AS NUM FROM ScoreAvg GROUP BY UserID) B
WHERE A.UserID=B.UserID
AND A.ID<>(SELECT TOP 1 ID FROM ScoreAvg WHERE UserID=A.UserID ORDER BY Score DESC)
AND A.ID<>(SELECT TOP 1 ID FROM ScoreAvg WHERE UserID=A.UserID ORDER BY Score)
GROUP BY A.UserID,B.NUM
from #A
group by userid
go
create table #A (ID int,UserID int,Score numeric(2,1),Judge varchar(1))
insert into #A
select 1,23,1.5,'A' union all
select 2,23,2.1,'B' union all
select 3,23,0.9,'C' union all
select 4,23,2.5,'D' union all
select 5,23,1.1,'E' union all
select 6,30,1.4,'A' union all
select 7,30,0.5,'B' union all
select 8,30,2.5,'C' union all
select 9,30,1.7,'D' union all
select 10,30,1.0,'E'select userid, (sum(score) - max(score) - min(score))/count(*) as avescore
from #A
group by userid
select userid, (sum(score) - max(score) - min(score))/(count(*)-2) as avescore
from #A
group by userid
就可以了
nihaoydh的思路不错!!!
from #A
group by userid