The usage of mysql case when is: 1. Used as a simple search, the syntax is [CASE [col_name] WHEN [value1]]; 2. Used as a search function, the syntax is [CASE WHEN [expr] THEN [ result1]].
【Related learning recommendations:mysql tutorial(Video)】
The usage of mysql case when is:
There are two syntaxes for case when
Simple function
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
Search function
CASE WHEN [expr] THEN [result1]…ELSE [default] END
What is the difference between these two syntaxes?
1. Simple function
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
Enumerate all possible values of this field*
SELECT NAME '英雄', CASE NAME WHEN '德莱文' THEN '斧子' WHEN '德玛西亚-盖伦' THEN '大宝剑' WHEN '暗夜猎手-VN' THEN '弩' ELSE '无' END '装备' FROM user_info; 复制代码 复制代码 SELECT NAME '英雄', CASE NAME WHEN '德莱文' THEN '斧子' WHEN '德玛西亚-盖伦' THEN '大宝剑' WHEN '暗夜猎手-VN' THEN '弩' ELSE '无' END '装备' FROM user_info;
2. Search function
CASE WHEN [expr] THEN [result1]…ELSE [default] END
The search function can write judgments, and the search function will only return the first value that meets the conditions, and other cases are ignored
# when 表达式中可以使用 and 连接条件 SELECT NAME '英雄', age '年龄', CASE WHEN age < 18 THEN '少年' WHEN age < 30 THEN '青年' WHEN age >= 30 AND age < 50 THEN '中年' ELSE '老年' END '状态' FROM user_info;
聚合函数 sum 配合 case when 的简单函数实现行转列 SELECT st.stu_id '学号', st.stu_name '姓名', sum( CASE co.course_name WHEN '大学语文' THEN sc.scores ELSE 0 END ) '大学语文', sum( CASE co.course_name WHEN '新视野英语' THEN sc.scores ELSE 0 END ) '新视野英语', sum( CASE co.course_name WHEN '离散数学' THEN sc.scores ELSE 0 END ) '离散数学', sum( CASE co.course_name WHEN '概率论与数理统计' THEN sc.scores ELSE 0 END ) '概率论与数理统计', sum( CASE co.course_name WHEN '线性代数' THEN sc.scores ELSE 0 END ) '线性代数', sum( CASE co.course_name WHEN '高等数学' THEN sc.scores ELSE 0 END ) '高等数学' FROM edu_student st LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id LEFT JOIN edu_courses co ON co.course_no = sc.course_no GROUP BY st.stu_id ORDER BY NULL;
Want to know more For programming learning, please pay attention to thephp trainingcolumn!
The above is the detailed content of What is the usage of mysql case when. For more information, please follow other related articles on the PHP Chinese website!