[Oracle] decode Function and Its Usage

Preface

DECODE () function, which compares the input value with the parameter list in the function and returns a corresponding value based on the input value. The parameter list of a function is a sequence of several numbers and their corresponding result values. Of course, if it fails to match any real argument sequence, the function also has the default return value.

The syntax structure is as follows:

decode (expression, search_1, result_1)decode (expression, search_1, result_1, search_2, result_2)decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)decode (expression, search_1, result_1, default)decode (expression, search_1, result_1, search_2, result_2, default)decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

Comparison expressions and search words. If they match, return results. If they do not match, return default values. If no default value is defined, return NULL values.

Instance usage

1. Create a student renewal table. Column: Student name, Subject name, and score

create table student_score(   name varchar2(30),   subject varchar2(20),   score number(4,1));insert into student_score (name,subject,score)values('zhang san','Chinese',90);insert into student_score (name,subject,score)values('zhang san','Mathematics',80);insert into student_score (name,subject,score)values('zhang san','English',79);

Test 1:

Select name, subject, decode (subject, 'China', score, 0) from student_score;

The result is as follows:

For a Chinese Course, the score is displayed. For other courses, the score is zero.

This SQL statement seems to be of little significance.

Test 2:

select name,sum(decode(subject, 'Chinese',score,0)) as CHINESE from student_score group by name;

Measure the scores of Chinese courses. It seems a little meaningful.

In general, decode usage looks a bit similar to case when. If it is used only in the above two situations, it does not seem significant.

select name,sum(decode(subject, 'Chinese',score,0)) as CHINESE from student_score group by name;select name,score as CHINESE from student_score;

The effect of using the two statements is the same, and decode seems to be the same. Row-to-column-meaningful use

Insert the scores of some other students into the above table:

insert into student_score (name,subject,score)values('li shi','Chinese',96);insert into student_score (name,subject,score)values('li shi','Mathematics',86);insert into student_score (name,subject,score)values('li shi','English',76);insert into student_score (name,subject,score)values('wang wu','Chinese',92);insert into student_score (name,subject,score)values('wang wu','Mathematics',82);insert into student_score (name,subject,score)values('wang wu','English',72); 

select name,sum(decode(subject, 'Chinese', nvl(score, 0), 0)) "Chinese",sum(decode(subject, 'Mathematics', nvl(score, 0), 0)) "Mathematics",sum(decode(subject, 'English', nvl(score, 0), 0)) "English"from student_scoregroup by name;

 

Return Value:
 

It makes sense to convert row data into columns.
 

Use case then to achieve the same effect.

select name,sum(case when subject='Chinese'              then nvl(score,0)         else 0    end) "Chinese",sum(case when subject='Mathematics'              then nvl(score,0)         else 0    end) "Mathematics",sum(case when subject='English'              then nvl(score,0)         else 0    end) "English"from student_scoregroup by name;