The rollup of the Oracle grouping function

Feature Description:

The first is a non-field aggregation , which is then aggregated after the fields are grouped from left to right

To create a table:

1 Create Table score 2 (3   Int , 4   Varchar2 (), 5   varchar2 (), 6   Int 7 );

View Code

Insert test data:

1 Insert  intoScoreValues(001,'Xiao Xu','language', the);2 Insert  intoScoreValues(001,'Xiao Xu','Mathematics',98);3 Insert  intoScoreValues(001,'Xiao Xu','Foreign Languages', About);4 Insert  intoScoreValues(002,'Xiao Wu','language', the);5 Insert  intoScoreValues(002,'Xiao Wu','Mathematics', About);6 Insert  intoScoreValues(002,'Xiao Wu','Foreign Languages', $);7 Insert  intoScoreValues(003,'Xiao Zhang','language', the);8 Insert  intoScoreValues(003,'Xiao Zhang','Mathematics', +);9 Insert  intoScoreValues(003,'Xiao Zhang','Foreign Languages', -);Ten Insert  intoScoreValues(004,'Xiao Sun','language', -); One Insert  intoScoreValues(004,'Xiao Sun','Mathematics', -); A Insert  intoScoreValues(004,'Xiao Sun','Foreign Languages', -); - Insert  intoScoreValues(001,'Xiao Peng','language', the); - Insert  intoScoreValues(001,'Xiao Peng','Mathematics', About); the Insert  intoScoreValues(001,'Xiao Peng','Foreign Languages', $); - Insert  intoScoreValues(004,'Leaflets','language', -); - Insert  intoScoreValues(004,'Leaflets','Mathematics', -); - Insert  intoScoreValues(004,'Leaflets','Foreign Languages', -); + Insert  intoScoreValues(003,'Xiao Liu','language', -); - Insert  intoScoreValues(003,'Xiao Liu','Mathematics', -); + Insert  intoScoreValues(003,'Xiao Liu','Foreign Languages', $); A Insert  intoScoreValues(002,'Child','language', the); at Insert  intoScoreValues(002,'Child','Mathematics', the); - Insert  intoScoreValues(002,'Child','Foreign Languages', the);

View Code

General grouping function, the total score of each class is counted:

Select Sum  from Group  by T.classid;

Query Result:

Plus Rollup, count the total score of each class and the total score of all classes:

Select Sum  from Group  by Rollup (T.CLASSID);

Query Result:

A non-field aggregation (1)is performed first, and then the ClassID Aggregation (3) is equivalent to:

1 Select Null Sum  from score T 2 Union  All 3 Select Sum  from Group  by T.classid;

Looking at two fields, count the total score of each class, the total score of all classes, and the overall scores of each student :

Select t.classid,t.studentname,SumfromGroup by

Query Result:

A non-field aggregation (1) is performed first, and then the Classid aggregation (3) is aggregated in combination with Classid and Studentname, which is equivalent to:

1 Select Null,Null,Sum(T.score) fromscore T2 Union  All3 SelectT.classid,Null,Sum(T.score) fromScore TGroup  byT.classid4 Union  All5 SelectT.classid, T.studentname,Sum(T.score) fromScore TGroup  byT.classid, T.studentname

The rollup of the Oracle grouping function