資料如下，統計每個人去掉兩個最高和最低的平均分
id name score
1 z1 33
2 z2 34
3 z3 35
4 z1 36
5 z2 37
6 z3 38
7 z1 39
8 z2 40
9 z3 41
10 z1 42
11 z2 43
12 z3 44
13 z1 45
14 z2 46
15 z3 47
16 z1 48
17 z2 49
18 z3 50
19 z1 51
20 z2 52
21 z3 53
得到
name score
z1 ??
z2 ??
z3 ??
uj5u.com熱心網友回復：
select (sum(score)-max(score)-min(score))/(count(*)-2),namefrom (
select * from
(select ROW_NUMBER() OVER(PARTITION BY name ORDER BY score) rn,name,score from temp)as a
where rn>=3 and rn<=？？？) ) t
group by name
可以這樣寫，但？？？這里不知道寫什么
uj5u.com熱心網友回復：
排序后用limit，偽代碼參考下:select * from (select ...
order by score)
limt 3, (select count(1)-4 ...)
uj5u.com熱心網友回復：
SELECT name,(SUM(score)-(SELECT SUM(score) FROM TestTable as B WHERE name=A.name AND score>(SELECT score FROM TestTable WHERE name=B.name ORDER BY score DESC LIMIT 2,1))
-(SELECT SUM(score) FROM TestTable as B WHERE name=A.name AND score<(SELECT score FROM TestTable WHERE name=B.name ORDER BY score LIMIT 2,1))
)/(COUNT(score)-4) AS FinalAvgScore
FROM TestTable A
GROUP BY name;
表名TestTable替換下~~，然后試試可行不
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/30037.html
標籤：MySQL