php / mysql多个顺序

Question

I am working on a results table at the minute, I am wanting to sort a table, by points (highest at the top), if points are equal I want to sort by goal difference, and then if goal difference is equal I want to sort by goals scored.

So a table may look like this,

+--------+--------+----------+-----------------+--------+
|  Team  | Scored | Conceded | Goal Difference | Points |
+--------+--------+----------+-----------------+--------+
| Team A |     20 |       10 | +10             |     15 |
| Team B |     20 |       15 | +5              |     15 |
| Team C |     10 |       10 | 0               |      9 |
| Team D |      5 |       5  | 0               |      9 |
+--------+--------+----------+-----------------+--------+

So Team A wins the league because it has a better goal difference than Team B, Team C finish above Team D because they score more goals, and all other things are equal.

Is it possible to order this way in mysql, or will I need to parse the results with PHP?

Answer 1

Guess what, you can pass multiple column names to ORDER BY

SELECT * FROM mytable ORDER BY Points DESC, `Goal Difference` DESC, Scored DESC 

You haven't given your table structure, but as pointed out by jpg if these fields are not numeric, ORDER by field_name + 0 may be more appropriate