Summary of methods for writing judgment statements in MySQL

How to write judgment statements in mysql:

Method 1. CASE function

case function syntax:

CASE condition
WHEN value1 THEN returnvalue1
WHEN value2 THEN returnvalue2
WHEN value3 THEN returnvalue3
…
ELSE defaultvalue
END

Example:

SELECT username,(CASE sex WHEN 1 THEN 'male' WHEN 2 THEN 'female' ELSE 'unknown' END) as sex FROM user;

The query results are as follows:

Method 2. IF() function

The CASE function can implement very complex logical judgments. If it implements a simple judgment logic like A if the condition is met, and B otherwise, the CASE function will appear bloated. MYSQL provides the IF() function to simplify this kind of logical judgment. Its syntax format is as follows: IF(condition,A,B)

If condition is true, return A, otherwise return B. So if there are multiple conditions, you cannot use the IF function. IF() returns a number or a string.

select username,if(sex=1,'男','女') as sex from user;

The query results are as follows:

As you can see, the username zhangsan is displayed as female because our condition is that sex=1 is male, and the others are female. Therefore, the IF function is used to judge only two situations. MySQL also has an IFNULL(value1, value2) function, which is generally used to replace NULL values. We know that NULL values ​​cannot participate in numerical operations.

Method 3. String operation ELT()

grammar:

ELT(N,str1,str2,str3,...)

If N = 1, return str1, if N = 2, return str2, and so on. If N is less than 1 or greater than the number of arguments, returns NULL.

SELECT username,ELT(sex,'男','女','未知') as sex FROM user

The query results are as follows:

Content extension:

mysql conditional statement

In business scenarios, for example, a user has several states (1: valid, 2: invalid, 3: other), and conditional statements are needed for queries.

grammar:

CASE field WHEN `condition 1` THEN `result 1`
 WHEN `condition 2` THEN `result 2`
 WHEN ...
 ELSE `Other results`
END

Example:

SELECT id,name,
(CASE status
	WHEN 1 THEN 'valid' 
	WHEN 2 THEN 'Invalid' 
ELSE 'Other' 
END) AS status 
FROM user

This is the end of this article summarizing the methods of writing judgment statements in MySQL. For more information about how to write judgment statements in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!