Description of the Msyql Field function [reprint], msyqlfield

Description of the Msyql Field function [reprint], msyqlfield

This function is relatively simple. Let's take a look at it:

 

MySQL can use the field () function to customize sorting. The format is field (value, str1, str2, str3, str4). The value is compared with str1, str2, str3, and str4, 1, 2, 3, and 4 are returned. If null is returned or data is not in the list, 0 is returned. this function seems to be not provided in Oracle (or I may not use it), but it is relatively simple to implement such a function by myself.

Mysql> select * from driver_log order by name;

+ -------- + ------- + ------------ + ------- +

| Rec_id | name | trav_date | miles |

+ -------- + ------- + ------------ + ------- +

| 1 | Ben | 152 |

| 9 | Ben | 2006-09-02 | 79 |

| 5 | Ben | 131 |

| 8 | Henry | 197 |

| 6 | Henry | 115 |

| 4 | Henry | 2006-08-27 | 96 |

| 3 | Henry | 300 |

| 10 | Henry | 203 |

| 7 | Suzi | 502 |

| 2 | Suzi | 391 |

+ -------- + ------- + ------------ + ------- +

10 rows in set (0.00 sec)

 

Mysql> select * from driver_log order by field (name, 'suzi', 'ben', 'henry ');

+ -------- + ------- + ------------ + ------- +

| Rec_id | name | trav_date | miles |

+ -------- + ------- + ------------ + ------- +

| 2 | Suzi | 391 |

| 7 | Suzi | 502 |

| 1 | Ben | 152 |

| 9 | Ben | 2006-09-02 | 79 |

| 5 | Ben | 131 |

| 8 | Henry | 197 |

| 6 | Henry | 115 |

| 4 | Henry | 2006-08-27 | 96 |

| 3 | Henry | 300 |

| 10 | Henry | 203 |

+ -------- + ------- + ------------ + ------- +

10 rows in set (0.00 sec)

 

Mysql> select * from driver_log order by field (name, 'suzi', 'ben ');

+ -------- + ------- + ------------ + ------- +

| Rec_id | name | trav_date | miles |

+ -------- + ------- + ------------ + ------- +

| 10 | Henry | 203 |

| 8 | Henry | 197 |

| 6 | Henry | 115 |

| 4 | Henry | 2006-08-27 | 96 |

| 3 | Henry | 300 |

| 7 | Suzi | 502 |

| 2 | Suzi | 391 |

| 5 | Ben | 131 |

| 9 | Ben | 2006-09-02 | 79 |

| 1 | Ben | 152 |

+ -------- + ------- + ------------ + ------- +

10 rows in set (0.00 sec)

 

Mysql> select * from driver_log order by field (name, 'suzi', 'ben') desc;

+ -------- + ------- + ------------ + ------- +

| Rec_id | name | trav_date | miles |

+ -------- + ------- + ------------ + ------- +

| 1 | Ben | 152 |

| 9 | Ben | 2006-09-02 | 79 |

| 5 | Ben | 131 |

| 2 | Suzi | 391 |

| 7 | Suzi | 502 |

| 8 | Henry | 197 |

| 6 | Henry | 115 |

| 4 | Henry | 2006-08-27 | 96 |

| 3 | Henry | 300 |

| 10 | Henry | 203 |

+ -------- + ------- + ------------ + ------- +

10 rows in set (0.00 sec)

 

Reproduced in: http://blog.csdn.net/lxz3000/article/details/6173873

References: https://segmentfault.com/a/1190000003742537