mysql custom sort

问题:

I have a query like this: SELECT * FROM table WHERE id IN (2,4,1,5,3);

However, when I print it out, it's automatically sorted 1,2,3,4,5. How can we maintain the order (2,4,1,5,3) without changing the database structure?

Thanks!

回答1:

i ask this :

mysql order by issue

the answers that i get and all the credit belong to them is :

You can use a CASE operator to specify the order:

SELECT * FROM table
WHERE id IN (3,6,1,8,9)
ORDER BY CASE id WHEN 3 THEN 1
                 WHEN 6 THEN 2
                 WHEN 1 THEN 3
                 WHEN 8 THEN 4
                 WHEN 9 THEN 5
         END

in php u can do it like :

<?php

$my_array =  array (3,6,1,8,9) ;

$sql = 'SELECT * FROM table  WHERE id IN (3,6,1,8,9)';

$sql .= "\nORDER BY CASE id\n";
foreach($my_array as $k => $v){
    $sql .= 'WHEN ' . $v . ' THEN ' . $k . "\n";
}
$sql .= 'END ';

echo $sql;

?>

回答2:

SELECT * FROM table WHERE id IN (2,4,1,5,3) ORDER BY FIELD(id,2,4,1,5,3);

Source: http://imthi.com/blog/programming/mysql-order-by-field-custom-field-sorting.php

回答3:

(I would have written this as a comment on Michel Tobon's answer, but don't have the reputation, sorry :-)

"And it worked... why? Beats me, but it just did; try it as well."

The reason that works is because your expression "code!='USA'" is producing a boolean result, which in SQL is represented as a 1 or 0. So, the expression "code='USA' produces a 1 for every record that matches that criterion, and a 0 for every record that does not. Because 1 is later than 0 in an ascending sort (the default), matching records will sort later than unmatching ones. Thus negating that expression producing the opposite effect.

Another (possibly clearer) way of producing the same result would be as follows:

SELECT * FROM countries ORDER BY code='USA' DESC, code='CAN' DESC, name ASC

Of course in answering the OP's question, I like the FIELD() option best - quite clean :-)

回答4:

The Ordering by field never worked for me. I had a list of countries and I needed United States and Canada to appear on top of the list, so my query was like this:

SELECT * FROM countries ORDER BY code='USA', code='CAN', name ASC

This never worked, but I realized that the ordering was different, it was showing Canada and the States at the end of the list, so I did this:

SELECT * FROM countries ORDER BY code!='USA', code!='CAN', name ASC

And it worked... why? Beats me, but it just did; try it as well.