Oracle statements add logical judgments-very practical ____oracle

  Oracle provides a number of logical judgment functions that can be used in queries.     1, testing for null values-NVL function     function prototype: NVL (testvalue,substitutevalue)     common usage is   Select max (score) from SC Where name= ' Jerry '     sometimes Max (score) is empty, which means that Jerry doesn't have an exam record, then we use "no Record "Callout:     Select NVL (Max (score)," No record ") from SC;       There is also a NVL2 function similar to it, the function prototype is: NVL (testvalue,subvalue1,subvalue2) NVL2 function to achieve if TestValue is null, Returns the SubValue1 or returns SUBVALUE2.     2, the broader sense of the test-decode function    Decode function of the prototype: Decode (TestValue, IF1, Then1, If2,then2.....else).    for TestValue test, if TestValue equals IF1 return then1, if TestValue equals If2 return then2,.... If none is returned, just return else.    common use is to implement row-columns in Oracle (Convert rows to Columns). class  course  student                                   
-----  ------   ------
One Engish D
One Engish F
One Engish E
Math F
Math D
Engish C
Engish B
History A
History B
Math B
Math C
I want to know how many students there are in the class 11 and 22 elective english,math,history.

SELECT course, SUM (DECODE (class, 1, 0)) as "Class-11", SUM (DECODE (class, 1, 0)) as "Class-22" From Studentinfo GROUP by Course

Here we first test the class, if 11, we return 1, if not 11 we return 0, and then sum the results, that is, you can know, 11 classes have a total number of students. Then by COURSE Group, you can draw the following: COURSE Class-11 Class-22
-------  --------   -----
Engish 3 2
Math 2 3 History 0 2 The solution has one limitation: you have to know in advance how many classes    But if these classes are not fixed, then the solution does not apply. For this limitation, the SQL section is not easily resolved and can be workaround in the program.
For example, now add a record of class number 33: Match A
When dynamically building SQL in a program, add it before the from language, SUM (DECODE (CLASS, 1, 0) as "Class-33") 3, a more readable logical test-case statement with a variable number of DECODE function parameters, which seems to be confusing.    Paste, Oracle provides another statement to achieve similar functionality, but is more readable. If we want to rename the Classmate field on the table: A:anco b:bily c:candy D:davi e:eve f:fion If we use decode we can do this:

Select class, course,        decode   (student,                 " A ',  ' Anco ',                 ' B ',  ' bily ',                  ' C ',  ' Candy ',                  ' D ',  ' Davi ',                  ' E ',  ' Eve ',                  ' F ',  ' fion '                )  as en_name   from studentinfo

The same can be achieved with the case statement:

SELECT CLASS, Course, (Case student When ' A ' THEN ' Anco ' When ' B ' THEN ' bily ' When ' C ' THEN ' Candy ' When ' D ' THEN ' Davi ' When ' E ' THEN ' Eve ' When ' F ' THEN ' fion ' End) as En_name From Studentinfo

    Turn from: http://blog.chinaunix.net/uid-576762-id-2733790.html               & nbsp