The use of select case when for oracle rookie learning

[toc]

The use of format syntax for oracle rookie learning select case when case when condition 1 then action1 when condition 2 then action2 when condition 3 then action3 when condition N then actionN else actionend example

Judge what month it is.

SQL > select case substr 2 when '08' then' 8yue'3 when '09' then' 9yue4 when '10' then' 10yue5 when '11' then' 11yue6 when '12' then' 7else 'other' 8 end 9 from dual;CASESUBSTR (' 201-11yueSQL >

Extended knowledge: substr interception

Sbustr ('str',x,y)

Str: string

X: start with the x bit

The end of the last y bit of the YRV x bit

SQL > select substr ('123456 recording 3 pencils 2) from dual;SUBSTR-34SQL > experiment

The experimental table is as follows:

Sno: student number

Km: subject

Score: grade

Grade: level

Create table score (sno number,km varchar2 (8), score int,grade varchar2 (4) default null); insert into score (sno,km,score) values (1 recollection values 65); insert into score (sno,km,score) values (2 recollection mahogany 76); insert into score (sno,km,score) values (3 recollection 86); insert into score (sno,km,score) values (4 recollection 94)

View tabl

SQL > select * from score SNO KM SCORE GRADE- 1 yw 65 2 sx 76 3 yw 86 4 yw 94

Question: grade the students' grades, excellent, good, medium and qualified

Idea: first check the corresponding grades of the student number.

SQL > select sno,case 2 when score > = 90 then'A'3 when score > = 80 then'B'4 when score > = 70 then'C'5 when score > = 60 then 'D'6 else' F' 7 end 8 from score; SNO CAS- 1 D 2C 3 B 4 A

Idea: how to insert a rating into a table?

Update score set grade =?

Idea: choose the value of the grade

Select grade from (select sno,case when score > = 90 then 'A'when score > = 80 then 'B'when score > = 70 then 'C'when score > = 60 then 'D'else' F'end as grade 9 from score); GRADE-DCBA

Idea: grade can not be equal to a set, can only be equal to a certain value, how to choose a certain value?

As you can see from the figure, if I alias the first table as a, but when a.sno and score.sno are equal, the value of grade is unique

Update score set grade = (select grade from (select sno,case when score > = 90 then 'A'when score > = 80 then 'B'when score > = 70 then 'C'when score > = 60 then 'D'else 'F'end as grade from score) a where a.sno=score.sno), 4 rows updated.

View the updated table

SQL > select * from score SNO KM SCORE GRADE--1 yw 65 D 2 sx 76 C 3 yw 86 B 4 yw 94 A