Mysql case when
时间: 2023-07-07 10:42:57 浏览: 51
MySQL CASE WHEN语句是一种流程控制语句,它允许您根据条件对结果进行分类。它的语法如下:
```
SELECT column_name,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
FROM table_name;
```
其中,column_name是要分类的列名,condition和result是条件和结果的列表,default_result是当没有条件匹配时的默认结果。根据情况,您可以设置任意数量的条件和结果。
例如,以下是一个简单的例子,根据成绩将学生分为三个等级:
```
SELECT name,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'D'
END AS grade
FROM student;
```
在这个例子中,当成绩大于等于90时,学生的等级为A,当成绩大于等于80时,学生的等级为B,以此类推。如果成绩不符合任何条件,则学生的等级为D。
相关问题
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是一种条件表达式,用于在查询语句中进行条件判断和返回不同的结果。它的语法如下:
CASE WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE result
END
\[1\]
在SELECT查询中,可以使用CASE WHEN来创建新的字段,根据条件返回不同的值。例如,可以使用CASE WHEN来替换字符串或进行分组排序。在WHERE子句中也可以使用CASE WHEN进行条件判断。在GROUP BY子句中使用CASE WHEN可以得到以前认为不可能得到的分组排序结果集。\[2\]\[3\]
#### 引用[.reference_title]
- *1* [Mysql中的CASE WHEN](https://blog.csdn.net/qq_44011569/article/details/130884794)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item]
- *2* *3* [MySQL 的CASE WHEN 语句使用说明](https://blog.csdn.net/lkx021699/article/details/120543003)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item]
[ .reference_list ]