Data as follows, each person to remove the two highest and the lowest score
Id name score
1 the 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
Get
The name score
Z1??
Z2??
Z3??

CodePudding user response:

Select (sum (score) - Max (score) - min (score))/(count (*) - 2), the name
The from (
Select * from
(select ROW_NUMBER () OVER (PARTITION BY the name the ORDER BY score) rn, name, score from temp) as a
Where rn>=3 and rn<=??? T))
Group by name
You can write, but??? Don't know what to write here

CodePudding user response:

Sorted using limit, pseudo code under reference:
Select * from (select...
The order by score)
Limt 3, (select count (1) - 4... )

CodePudding user response:

SELECT the name, (SUM (score)
- (SELECT SUM (score) FROM TestTable as B WHERE name=A.n ame AND score> (SELECT score FROM TestTable WHERE name=B.n ame ORDER BY score DESC LIMIT 2, 1))
- (SELECT SUM (score) FROM TestTable as B WHERE name=A.n ame AND score<(SELECT score FROM TestTable WHERE name=B.n ame ORDER BY score LIMIT 2, 1))
)/(COUNT (score) - (4) the AS FinalAvgScore
The FROM TestTable A
GROUP BY name;
The table name TestTable replace ~ ~ and don't try feasible