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.

How to use case when in mysql

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!