Mysql function usage record (2) -- ELT (), FIELD (), IFNULL (), eltifnull

Mysql function usage record (2) -- ELT (), FIELD (), IFNULL (), eltifnull

Yesterday, I thought of using DECODE () to implement the effect in the process of modifying a business. I suddenly found that the Mysql database is currently used. After checking it, I finally used ELT (), FIELD () and IFNULL () functions. Make a record for it.

Syntax:

ELT (n, str1, str2, str3,...): returns str1 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 parameters, NULL is returned. ELT () is a complementary function of FIELD.

mysql> SELECT ELT(3,'hello','halo','test','world');+--------------------------------------+| ELT(3,'hello','halo','test','world') |+--------------------------------------+| test                                 |+--------------------------------------+1 row in setmysql>

FIELD (str, str1, str2, str, str3, str4 ...): return the parameter columns (str1, str2, str, str3, str4...) after str ...) the START index is 1. If str is not found in the parameter column, 0 is returned.

mysql> SELECT FIELD('halo','hello','halo','test','world');+---------------------------------------------+| FIELD('halo','hello','halo','test','world') |+---------------------------------------------+|                                           2 |+---------------------------------------------+1 row in setmysql> 

IFNULL (expr1, expr2): If expr1 is NULL, expr2 is returned. If expr1 is not NULL, expr1 is returned. IFNULL () returns a number or string value, depending on the context in which it is used.

mysql> SELECT IFNULL(NULL,8);+----------------+| IFNULL(NULL,8) |+----------------+|              8 |+----------------+1 row in setmysql> SELECT IFNULL('hello','world');+-------------------------+| IFNULL('hello','world') |+-------------------------+| hello                   |+-------------------------+1 row in setmysql> SELECT IFNULL(null,'world');+----------------------+| IFNULL(null,'world') |+----------------------+| world                |+----------------------+1 row in setmysql> 

Application:

For example, if the source type encoding of the Order is stored in the order record, the source name must be displayed in the list of pages. The source information is not separately stored in tables, you need to modify the query statement for the following purposes:

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

In Mysql, we need ELT () and FIELD () to implement it. If necessary, IFNULL () will be used to achieve the final effect. For the above requirements, we can use the following SQL:

SELECT *, IFNULL (ELT (FIELD (type, '1', '2', '3', '4'), 'source A', 'source B ', 'source C', 'source D'), 'unknown ') name from order;

Record complete! Pai_^ ~