mysql 自定义排序

mysql 自定义排序

一、mysql field自定义排序函数的格式

order by field(value,str1,str2,str3,str4,,,,,,strn)

其中value后面的参数自定义,不限制参数个数

含义

将获取出来的数据根据str1,str2,str3,str4等的顺序排序

二、mysql order by field 实例

1、按 id 以 5, 3, 7, 1 的顺序排列

  •  
  • 
    SELECT * FROM `MyTable` 
    WHERE `id` IN (1, 7, 3, 5) 
    ORDER BY FIELD(`id`, 5, 3, 7, 1)
    
    		
  • 2、按 name 以 'Suzi','Ben','Henry' 的顺序排列

  •  
  • 
    mysql> select * from driver_log order by field(name,'Suzi','Ben','Henry');
    
    
    
    --结果
    
    
    +--------+-------+------------+-------+
    
    | rec_id | name  | trav_date  | miles |
    
    +--------+-------+------------+-------+
    
    |      2 | Suzi  | 2006-08-29 |   391 |
    
    |      7 | Suzi  | 2006-09-02 |   502 |
    
    |      1 | Ben   | 2006-08-30 |   152 |
    
    |      9 | Ben   | 2006-09-02 |    79 |
    
    |      5 | Ben   | 2006-08-29 |   131 |
    
    |      8 | Henry | 2006-09-01 |   197 |
    
    |      6 | Henry | 2006-08-26 |   115 |
    
    |      4 | Henry | 2006-08-27 |    96 |
    
    |      3 | Henry | 2006-08-29 |   300 |
    
    |     10 | Henry | 2006-08-30 |   203 |
    
    +--------+-------+------------+-------+
    
    		
  • 标签: