Mysql function Usage record (ii)--elt (), FIELD (), Ifnull ()

Yesterday in the process of a business modification of the thought of using decode () to achieve the results, in the blink of an eye found that the current use of MySQL library, after consulting, the final use of ELT (), FIELD (), ifnull () function to achieve the requirements. Now make a record of it.

Grammar:

ELT (n,str1,str2,str3,...) : If n=1, returns STR1 if n=2, returns STR2, and so on. If n is less than 1 or greater than the number of arguments, NULL is returned. ELT () is the function complement function of field ().

Mysql> SELECTELT (3,'Hello','Halo','Test',' World');+--------------------------------------+|ELT (3,'Hello','Halo','Test',' World')|+--------------------------------------+|Test|+--------------------------------------+1Rowinch SetMySQL>

FIELD (STR,STR1,STR2,STR,STR3,STR4 ...): Returns STR in the following parameter column (STR1,STR2,STR,STR3,STR4 ... ), with a starting index of 1. If STR is not found in the parameter sequence, 0 is returned.

Mysql> SELECTFIELD ('Halo','Hello','Halo','Test',' World');+---------------------------------------------+|FIELD ('Halo','Hello','Halo','Test',' World')|+---------------------------------------------+|                                           2 |+---------------------------------------------+1Rowinch SetMySQL> 

ifnull (EXPR1,EXPR2): If EXPR1 is null, EXPR2 is returned and EXPR1 is returned if EXPR1 is not null. Ifnull () returns a numeric or string value, depending on the context in which it is used.

Mysql> SELECTIfnull (NULL,8);+----------------+|Ifnull (NULL,8)|+----------------+|              8 |+----------------+1Rowinch SetMySQL> SELECTIfnull ('Hello',' World');+-------------------------+|Ifnull ('Hello',' World')|+-------------------------+|Hello|+-------------------------+1Rowinch SetMySQL> SELECTIfnull (NULL,' World');+----------------------+|Ifnull (NULL,' World')|+----------------------+|World|+----------------------+1Rowinch SetMySQL> 

Application:

For example, the source type encoding of the order is stored in the order record, the source name is displayed in the page list, the source information is not stored separately, and the query statement needs to be modified to achieve the purpose:

In Oracle we can use the Decode () function to get to the source name: Decode (condition, value 1, return value 1, value 2, return value 2 ..., default value);

In MySQL, we need ELT (), FIELD () to implement, and ifnull () when necessary to achieve the final effect. For the above requirements, we can take the following SQL to achieve:

SELECT*,ifnull (ELT (FIELD (Type,'1','2','3','4'),'Source A','Source B','Source C','Source D'),'Unknown') name from Order;

The record is complete! ^_^~

　　

Mysql function Usage record (ii)--elt (), FIELD (), Ifnull ()