Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

The data table structure used in this section is as follows:

The data content is as follows:

1 the if () function

Its usage is as follows:

If (condition, a, b): Returns a if condition is true, b otherwise

1.1 Basic Usage

SELECT id, score, IF (score >= 60, "pass "," fail ") AS score_result FROM 'chapter8'Copy the code

result:

1.2 If multi-layer nesting

SELECT id, score, IF (score < 60, "failing ", IF (score < 80," good ", "excellent")) AS score_result FROM 'chapter8'Copy the code

result:

2 Case when function

2.1 Make multiple judgments for a column

Its form is as follows:

Case Column name when Condition 1 then Return value 1 When Condition 2 then return value 2...... When condition n then return value n else Return default value endCopy the code

The conditions in this form can only be concrete values, not comparison operations

SELECT id, Class,(CASE class WHEN "class1" THEN "class1" THEN "class2" WHEN "class3" THEN "class3" ELSE "END) class_result FROM chapter8Copy the code

result:

2.2 Comparison operation

Its form is as follows:

Case when the column name meets condition 1 then The return value 1 when the column name meets condition 2 then the return value 2...... When column names satisfy the condition n then return value n else Return default value endCopy the code

In this form, column name comparison is supported

SELECT ID, score,(CASE WHEN score < 60 THEN "failing" WHEN score < 80 THEN "good" ELSE "excellent" END) score_result FROM chapter8Copy the code

result: