In the actual development, sometimes there will be let you do statistics, make statements.
For example, in the Student elective management system allows you to write all the students of each of the results, at this time we write the results of the SQL is generally a student selected a lot of classes, each one of the classes and corresponding results are a line of records.
Format as follows:
NAME COURSE gread
---------------
dai java c # dai c
# Dai SQL 90
Often the format above is not what we want, the format we want is as follows:
NAME JAVA C # c SQL
---------------
dai 90
Here we construct a complete example to see how the above situation is implemented:
CREATE TABLE t (
name VARCHAR2 (a),
course VARCHAR2 (a),
gread number
)
BEGIN
INSERT into t VALUES (' Dai ', ' Java ',);
INSERT into T VALUES (' Dai ', ' C # ',);
INSERT into T VALUES (' Dai ', ' C ', #);
INSERT into T VALUES (' dai ', ' sql ', ');
INSERT into T VALUES (' tu ', ' Java ', #);
INSERT into T VALUES (' tu ', ' C # ',);
INSERT into T VALUES (' tu ', ' C ', n);
INSERT into T VALUES (' tu ', ' sql ',);
End;
The code above constructs the data.
Below we divide into two parts to realize student's achievement report:
1, through the use of decode to achieve row and column conversion, query each student selected courses and corresponding results.
SELECT name,
DECODE (course, ' Java ', gread) as Java,
DECODE (course, ' C # ', Gread) as C #,
DECODE (course, ' C ', GR EAD) as C,
DECODE (Course, ' SQL ', Gread) as SQL from
T;
NAME JAVA C # C SQL
--------------------------------------------------
dai 60 Dai-Dai-Dai-Tu-Tu-
Tu-
tu 60
2, by using the aggregate function of Max () to implement the scatter to the whole
With the results of the first step, we found that using decode constructs a row with the course field, but we found that each course and corresponding score still appeared on one line. But if you look carefully, it's a line to just compress each line of the same name into one. How are we going to do that? Here we should think of using grouping (group by) to implement.
SELECT name,
Max (DECODE (course, ' Java ', Gread)) as Java,
Max (DECODE (course, ' C # ', Gread)) as C #,
Max (DECODE (Course, ' C ', Gread) As C,
MAX (DECODE (Course, ' SQL ', Gread)) as SQL from-
t
GROUP by name;
NAME JAVA C # C SQL
--------------------------------------------------
dai 60 tu ( 60)
Can you see the results we want?
Now is not the fashion to say, "Then, the problem is coming." "That sentence. Do. For the attentive person, then the problem comes.
Here's why we use Max () instead of aggregate functions like sum (), AVG (), and so on. Tell everyone that in the example, the above 3 aggregate functions are actually available and can be implemented successfully. But if the value in the aggregate function is the character of the use of sum (), AVG () is OK. The answer is no, so using Max () is a compatible consideration.