在PHP MYSQL中排序SQL语句

Question

I have a table called email from which I fetch email. The table has email in this order

1. [e-mail]

   [e-mail]
   [e-mail]

Now If I make this query

SELECT * FROM email WHERE (email IN ( '[e-mail]' ,'[e-mail]' ,  '[e-mail]' )) AND email!='' LIMIT 3

I get this

[e-mail]

[e-mail]

[e-mail]

but I want to get result on the basis of the arguments in the IN clause If you notice in the IN I mentioned [e-mail] first, I dont think order by clause will help me here Any other way of solving this issue?

Answer 1

One possible approach is using FIELD() MySQL function:

   SELECT * 
     FROM email
    WHERE FIELD(email, '[e-mail]', '[e-mail]', '[e-mail]') <> 0 
 ORDER BY FIELD(email, '[e-mail]', '[e-mail]', '[e-mail]');

As FIELD returns 0 if its first argument isn't equal to any other, FIELD(str, 'aaa', 'bbb'...) <> 0 clause is roughly equivalent to str IN ('aaa', 'bbb').