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

This article mainly introduces how to use the Case sentence in SQL, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

SQL Case statement syntax

There are many things in the syntax, but it is still quite straightforward: the keyword CASE indicates the beginning of the case statement, and the keyword END indicates the end of it.

Then for a single condition, you can write a keyword, WHEN followed by the condition that must be met. This is followed by the keyword and value of the THEN condition, such as WHEN THEN.

Then you can follow other WHEN/THEN statements.

Finally, if all the conditions of the ELSE keyword are not true, you can add a default value, as shown below.

CASE WHEN condition1 THEN stuff WHEN condition2 THEN other stuff... ELSE default stuffEND

Let's put it into practice to better understand it.

Example of SQL Case statement

Let's CASE use this statement in the example. We have a table that lists the students and their exam results. We need to grade each student, and we can use the case statement to do it automatically.

ID name score 1 Simi Sola 602 Ivan 803 Metodia 524 Callum 985 Leia 846 Apalesi 827 Ursula 698 Ramadan 789 Corona 8710 Alice 5711 Karantriel 8912 Merrell 9913 Shirley 5514 Niti 8115 Elsa 7116 Lis 9017 Johanna 9018 Ampheza 9019 Sandchai 6121 Elbert 6322 Catelyn 51

We can use this CASE statement to give each student a grade, and we will add that grade grade in the new column named.

Let's write down the CASE statement first, in which we will write down the breakdown of each grade. When score is 94 or higher, the value of the row is A. If the score is 90 or higher, the value is Amure, and so on.

CASE WHEN score > = 94 THEN "A" WHEN score > = 90 THEN "A -" WHEN score > = 87 THEN "B+" WHEN score > = 83 THEN "B" WHEN score > = 80 THEN "B -" WHEN score > = 77 THEN "C +" WHEN score > = 73 THEN "C" WHEN score > = 70 THEN "C -" WHEN score > = 67 THEN "D+" WHEN score > = 60 THEN "D" ELSE "F" END

After writing the CASE statement, we will add it to the query. Then we name the column using the AS keyword grade:

SELECT *, CASE WHEN score > = 94 THEN "A" WHEN score > = 90 THEN "A -" WHEN score > = 87 THEN "B+" WHEN score > = 83 THEN "B" WHEN score > = 80 THEN "B -" WHEN score > = 77 THEN "C +" WHEN score > = 73 THEN "C" WHEN score > = 70 THEN "C -" WHEN score > = 67 THEN "D+" WHEN score > = 60 THEN "D" ELSE "F" END AS gradeFROM students_grades

The table we get from this query is as follows-each student now has a grade based on their score.

ID name score Grade 1 Simi Sola 60D2 Ivan 80 B-3 Metodia 52F4 Callum 98 a 5 Leia 84 B 6 Apalesi 82 B-7 Ursula 69D+8 Ramadan 78C+9 Corona 87 B + 10 Alice 57F11 Kellantrel 89 B + 12 Merel 99 a 13 Shirley 55F14 Niti 81 B-15 Elsade 71C-16lis 90 a 17 Johanna 90 a 18 Amphesa 90 a 19 cooler 97 Examples of more complex Case statements of 20 kinds of sand guesses 61D21 Albert 63D22 Catelyn 51F

We can also manipulate the table in different ways using statements other than case statements as needed.

Case statement example 1

For example, we can use ORDER BY to sort rows to put the highest score first.

SELECT name, CASE WHEN score > = 94 THEN "A" WHEN score > = 90 THEN "A -" WHEN score > = 87 THEN "B+" WHEN score > = 83 THEN "B" WHEN score > = 80 THEN "B -" WHEN score > = 77 THEN "C +" WHEN score > = 73 THEN "C" WHEN score > = 70 THEN "C -" WHEN score > = 67 THEN "D+" WHEN score > = 60 THEN "D" ELSE "F" END AS gradeFROM students_gradesORDER BY score DESC

We sort by which score is a number rather than a grade column, because the alphabetical order is different from the value-based hierarchical order. We use the DESC keyword to render it in descending order, with the highest value at the top.

The table we get is as follows:

A kind of name grade Merel, a kind of cold introduction, a kind of Lis, a kind of Johanna, a kind of Anfeisa, a kind of Karandriel B + Corona B + Leia B Aparesida B-Nitya B-Ivan B-Ramadan C + El Saad C-Ursula D + Elbert D Shaqai D Simi Sola D Alice F Shirley F Metodia F Catelyn FCase sentence example 2

Let's do some analysis of these data. We can use GROUP BY and COUNT to calculate how many students we receive in each grade.

SELECT CASE WHEN score > = 94 THEN "A" WHEN score > = 90 THEN "A -" WHEN score > = 87 THEN "B+" WHEN score > = 83 THEN "B" WHEN score > = 80 THEN "B -" WHEN score > = 77 THEN "C +" WHEN score > = 73 THEN "C" WHEN score > = 70 THEN "C -" WHEN score > = 67 THEN "D+" WHEN score > = 60 THEN "D" ELSE "F" END AS grade COUNT (*) AS number_of_studentsFROM students_gradesGROUP BY gradeORDER BY score DESC

We use ORDER BY to sort the grades from highest to lowest, and we use score, which is a numeric value (because sorting by grade column uses alphabetical order, which is different from sorting by the value of the rank).

Grade NUMBER_OF_STUDENTS A 3 one-3 B + 2 B 1 B-3C+1C-1D+1D3F4 case statement example 3

Let's do some different analysis of these data. We can use GROUP BYandCOUNT and a different case statement to calculate how many students have passed the exam. Then we can use ORDER BY to arrange the columns in the order we like, through the number of students at the top.

SELECT CASE WHEN score > = 60 THEN "passed" ELSE "failed" END AS result, COUNT (*) AS number_of_studentsFROM students_gradesGROUP BY resultORDER BY result DESC

The table we got is as follows. The class performed well, with 18 of the 22 students passing the grade-but the other four might need some help.

As a result, NUMBER_OF_STUDENTS thanks you through 18 failed 4 for reading this article carefully. I hope the article "how to use Case sentences in SQL" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.