select *,if(sva=1,"male","female") as ssva from taname where sva<>""
12.2. Control flow function
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
In the return result of the first scenario, value=compare-value. The return result of the second scenario is the true result of the first scenario. If there is no matching result value, the return result is the result after ELSE. If there is no ELSE part, the return value is NULL.
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
-> WHEN 2 THEN 'two' ELSE 'more' END;
-> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
-> 'true'
mysql> SELECT CASE BINARY 'B'
-> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
-> NULL
The default return value type of a CASE expression is any compatible collection type of the return value, but The specific situation depends on the context. If used in a string context, returns the resulting string. If used in a numeric context, the result is returned as a decimal, real, or integer value.
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), the return value of IF() is expr2; otherwise, the return value is expr3. The return value of IF() is either a numeric value or a string value, depending on the context.
mysql> SELECT IF(1>2,2,3);
-> 3
mysql> SELECT IF(1<2,'yes ','no');
-> 'yes'
mysql> SELECT IF(STRCMP('test' ,'test1'),'no','yes');
-> 'no'If
only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the result type of the non-NULL expression.
expr1 evaluates as an integer value, which means that if you are validating a floating point value or a string value, you should use comparison operations to check.
mysql> SELECT IF(0.1,1,0);
-> 0
mysql> SELECT IF(0.1<>0,1,0);
-> 1
In the first example shown, the return value of IF(0.1) is 0 because 0.1 is converted to an integer value, causing a test of IF(0). This may not be the situation you want. In the second example, the comparison checks the original floating point value to see if it is non-zero. Use integers for comparison results.
The default return type of IF() (this is important when it is stored in a temporary table) is calculated as follows:
expression
return value
The return value of expr2 or expr3 is a string.
string
The return value of expr2 or expr3 is a floating point value.
floating point
The return value of expr2 or expr3 is an integer.
integer
If expr2 and expr3 are both strings, and either string is case-sensitive, the returned result is case-sensitive.
http://blog.knowsky.com/
IFNULL(expr1,expr2)
If expr1 is not NULL, the return value of IFNULL() is expr1; otherwise, the return value is expr2. The return value of IFNULL() is either a number or a string, depending on the context in which it is used.
mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0, 'yes');
-> 'yes'
The default result value of IFNULL(expr1,expr2) is the more "general" one of the two expressions, in the order of STRING, REAL or INTEGER. Assume the case of an expression-based table, or MySQL must store the return value of IFNULL() in a temporary table in memory:
CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
in this example, test The column type is CHAR(4).
NULLIF(expr1,expr2)
If expr1 = expr2 holds, the return value is NULL, otherwise the return value is expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
mysql> SELECT NULLIF(1,1);
-> NULL
mysql> SELECT NULLIF(1,2);
-> 1
Note that if the parameters are not equal, the value obtained by MySQL twice is expr1.