Shulou(Shulou.com)06/01 Report--

What is the role of case when in mysql? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

The usage of mysql case when is: 1, as a simple search, the syntax is [CASE [col_name] WHEN [value1]]; 2, as a search, the syntax is [ASE WHEN [expr] THEN [result1]].

[recommended for related study: mysql tutorial (video)]

The usage of mysql case when is:

There are two syntax for MySQL's 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's the difference between the two grammars?

Simple function

CASE [col_name] WHEN [value1] THEN [result1]... ELSE [default] END: enumerate all possible values for this field *

SELECT NAME 'hero', CASE NAME WHEN 'Dreven' THEN 'axe' WHEN 'Demacia-Galen' THEN 'sword' WHEN 'night hunter-VN' THEN' crossbow 'ELSE' no 'END' equipment 'FROM user_info Copy code copy code SELECT NAME 'hero', CASE NAME WHEN 'Delavan' THEN 'axe' WHEN 'Demacia-Galen' THEN 'sword' WHEN 'night hunter-VN' THEN' crossbow 'ELSE' no 'END' equipment 'FROM user_info

Search function

CASE WHEN [expr] THEN [result1]... ELSE [default] END: the search function can write judgment, and the search function will only return the first qualified value, and other case will be ignored.

In # when expressions, you can use and to connect conditions SELECT NAME 'hero', age 'age', CASE WHEN age

= 30 AND age < 50 THEN 'middle age' ELSE 'old age' END 'state' FROM user_info The aggregate function sum cooperates with the simple function of case when to realize the row transfer of SELECT st.stu_id 'student number', st.stu_name 'name', sum (CASE co.course_name WHEN 'college Chinese' THEN sc.scores ELSE 0 END) 'college Chinese' Sum (CASE co.course_name WHEN 'New Horizon English' THEN sc.scores ELSE 0 END) 'New Horizon English', sum (CASE co.course_name WHEN 'THEN sc.scores ELSE 0 END)' discrete Mathematics' Sum (CASE co.course_name WHEN 'probability theory and mathematical statistics' THEN sc.scores ELSE 0 END) 'probability theory and mathematical statistics', sum (CASE co.course_name WHEN 'linear algebra' THEN sc.scores ELSE 0 END) 'linear algebra' Sum (CASE co.course_name WHEN 'higher mathematics' THEN sc.scores ELSE 0 END) 'higher mathematics' FROM edu_student stLEFT JOIN edu_score sc ON st.stu_id = sc.stu_idLEFT JOIN edu_courses co ON co.course_no = sc.course_noGROUP BY st.stu_idORDER BY NULL Thank you for reading! After reading the above, do you have a general understanding of the role of case when in mysql? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.