Home >Database >Mysql Tutorial >How to use mysql case when
In mysql, "case when" is used to calculate a condition list and return one of multiple possible result expressions; "case when" has two syntax formats: 1. Simple function "CASE[col_name]WHEN [value1]THEN[result1]…ELSE[default]END”; 2. Search function “CASE WHEN[expr]THEN[result1]…ELSE[default]END”.
The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.
The case when statement is used to calculate a list of conditions and return one of multiple possible result expressions.
CASE comes in two formats: Simple CASE function compares an expression to a set of simple expressions to determine the result. The CASE search function evaluates a set of Boolean expressions to determine a result. Both formats support the optional ELSE parameter.
When doing data analysis, you need to find data from the database and perform analysis and display. Accurate data query results are conducive to concise and effective analysis in the later period. During this period, case when in mysql was used. Record the usage of case when.
The CASE statement is a multi-branch statement structure. The statement first searches for a value equal to the VALUE after CASE from the VALUE after WHEN. If found, the content of the branch is executed, otherwise the content after ELSE is executed. .
MySql has two case when syntaxes:
1. Simple function
CASE[col_name]WHEN[value1]THEN[result1]…ELSE[default]END
2. Search function
CASE WHEN[expr]THEN[result1]…ELSE[default]END
Simple function
CASE input_expression WHEN when_expression THEN result_expression [...n ] [ ELSE else_result_expression END
Enumerate all possible values of Input_expression.
The data types of Input_expression and each when_expression must be the same, or they must be implicitly converted.
Search function
CASE WHEN [Boolean_expression] THEN [result1]…ELSE [default] END
Judge the Boolean_expression expression, if it is satisfied, return The result after then
Take the personnel table as an example
The first usage, when must be followed by the value of the case column, not an expression
select case sex when sex='1' then '男' when sex='2' then '女' else '其他' end as '性别' from people; select case sex when sex='2' then '女' when sex='1' then '男' else '其他' end as '性别' from people;
Query results
Reason: Because when is followed not by all the possible values of the case column, but by an expression, why does a result like 'male' appear? , need to think about it, and then update it after researching and understanding it later
Correct writing
select case sex when '1' then '男' when '2' then '女' else '其他' end as '性别' from people;
Query results
Recommended learning:mysql Video tutorial
The above is the detailed content of How to use mysql case when. For more information, please follow other related articles on the PHP Chinese website!