MYSQL：除最高值和最低值之外的总值，然后乘以

I have stumbled upon a problem that I can't get my head around.

I have the following topic related tables:

dive(ID,Type,Diver_id,Difficulty,Contest_id)

results(ID,Points,Judge_Id,Dive_id)

divers(ID,Name,Country)

My SELECT statement is meant to list the diver.Name, diver.Country and "total points"

The total points is supposed to be (total += ((dive_total -min -max)*difficulty))

Let's say a diver did 3 dives. Every dive has been rated by ~4 judges and every dive has it's own difficulty. The lowest/highest score per dive is supposed to be removed. Then added together, multiplied with the difficulty and then added to the persons total points.

I have got a list that shows diver.Name, diver.Country and Total. But the total is only the actual total without the subtraction and multiplication.

Query:

SELECT
  divers.Name,
  divers.Country,
  SUM(results.Points) AS Total
FROM results
  INNER JOIN dive
     ON results.Dive_id = dive.ID
   INNER JOIN divers
     ON dive.Diver_id = divers.ID
WHERE dive.Contest_id = '1'
GROUP BY divers.Name
ORDER BY Total DESC

Don't mind the contest_id. It's always '1'.

Please ask for more information if needed. Maybe the question is very silly. I am not a advanced "database guy". Sorry for bad English in advance.

(It is later printed in php/html. Maybe a language combination between mysql and php is a better way to move forward?)

I think what you want is this

SELECT
  divers.Name,
  divers.Country,
  ((SUM(results.Points) - MAX(results.Points) - MIN(results.Points)) *  dive.Difficulty) AS Total
FROM results
  INNER JOIN dive
     ON results.Dive_id = dive.ID
   INNER JOIN divers
     ON dive.Diver_id = divers.ID
WHERE dive.Contest_id = '1'
GROUP BY divers.Name
ORDER BY Total DESC

Otherwise I would just change the SELECT to include:

MIN(results.Points) as maxPoints,
MAX(results.Points) as minPoints,
SUM(results.Points) as totalPoints

and then do the math side of it using PHP

Thank you ode2k! Very helpful. The end result with your code + some GROUP BY

 SELECT
  divers.Name,
  divers.Country,
 SUM(results.Points * dive.Difficulty) - MAX(results.Points *    dive.Difficulty) - MIN(results.Points * dive.Difficulty) AS Total
FROM results
  INNER JOIN dive
    ON results.Dive_id = dive.ID
  INNER JOIN divers
    ON dive.Diver_id = divers.ID
WHERE dive.Contest_id = '1'
GROUP BY divers.Name,
         dive.ID,
         dive.Difficulty,
     results.Dive_id
ORDER BY Total DESC

And then I added the the scores together in PHP to get every name just once.