mysql 的 case when
时间: 2024-05-23 14:08:48 浏览: 16
MySQL的CASE WHEN语句是一种控制流语句,它允许你根据条件来执行不同的操作。当你需要在查询结果中根据条件对某些列进行分类或排序时,CASE WHEN语句就派上用场了。
它的基本语法如下:
```
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END
```
其中,`condition1`、`condition2`等是可以使用比较运算符、逻辑运算符等多种条件组合而成的表达式,`result1`、`result2`等则是当相应条件成立时返回的结果值。如果所有条件都不成立,则返回`resultN`。
此外,CASE WHEN还支持嵌套使用,可以构建更加复杂的逻辑。
例如,以下是一个使用CASE WHEN语句进行分类的示例:
```
SELECT
name,
CASE
WHEN score >= 90 THEN '优秀'
WHEN score >= 80 THEN '良好'
WHEN score >= 60 THEN '及格'
ELSE '不及格'
END AS grade
FROM
student;
```
以上语句将根据学生的分数将其分为优秀、良好、及格和不及格四个等级,并将结果作为新列grade返回。
相关问题
mysql case when
MySQL CASE WHEN statement is a conditional statement that evaluates multiple conditions and returns a value based on the first condition that is true.
The basic syntax for the MySQL case when statement is as follows:
```
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE default_result
END
```
Here is an example of how to use the MySQL case when statement:
```
SELECT first_name, last_name,
CASE
WHEN age < 18 THEN 'Minor'
WHEN age BETWEEN 18 AND 65 THEN 'Adult'
ELSE 'Senior'
END AS age_group
FROM customers;
```
In this example, we are selecting the first name, last name, and age group of customers from the customers table. We use the MySQL case when statement to evaluate the age of each customer and assign them to an age group based on their age. If the customer is under 18, they are assigned to the 'Minor' group, if they are between 18 and 65, they are assigned to the 'Adult' group, and if they are over 65, they are assigned to the 'Senior' group.
The resulting output would look something like this:
```
+------------+-----------+-----------+
| first_name | last_name | age_group |
+------------+-----------+-----------+
| John | Doe | Adult |
| Jane | Smith | Senior |
| Bob | Johnson | Minor |
| Sarah | Lee | Adult |
+------------+-----------+-----------+
```
Mysql case when
MySQL CASE WHEN语句可以用于在SQL查询中执行条件逻辑。它的基本语法如下:
```
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE result
END
```
其中,`condition1`、`condition2`等是条件表达式,`result1`、`result2`等是对应条件表达式成立时的结果。如果所有条件都不成立,则返回`ELSE`后面的`result`。
例如,下面的查询将根据`price`列的值返回`cheap`、`moderate`或`expensive`:
```
SELECT name, price,
CASE
WHEN price < 50 THEN 'cheap'
WHEN price < 100 THEN 'moderate'
ELSE 'expensive'
END AS price_range
FROM products;
```
这个查询将返回一个包含`name`、`price`和`price_range`列的结果集,其中`price_range`列是根据`price`列的值计算出来的。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)