设置一个Mysql查询以按特定顺序显示数字列？ [重复]

Question

This question already has an answer here:

• Returning query results in predefined order 11 answers

I have a user input box, querying a column of numbers in my SQL database, which, for example are:

2.0
3.0
4.0
5.0
6.0
7.0
8.0

The user will have to type in a number, and then populate a particular order of the list of numbers to respond to the input from the user. So if the user input was 4.0, then i want the database query to display

4.0,3.0,5.0,2.0,6.0,7.0,8.0.  

To break the results down, I want the result pattern to display the database results with the closest number to the input(4.0),then the next result, if available, to be lower by 1 degree(3.0), then higher by 1 degree(5.0),then lower by two degrees (2.0), and higher by two degrees(6.0). The 7.0 and 8.0 numbers are displayed numerically because there is no lows to match them.

How can I setup a query for this pattern of results?

</div>

Answer 1

SELECT  *
FROM    mytable
ORDER BY
        ABS(value - 4.0), SIGN(value - 4.0)

See SQLFiddle