Oracle Advanced Query GROUP BY

The approach to group by is no longer burdensome, as is the case in advanced mode.

Reference teacher article: http://blog.csdn.net/fu0208/article/details/7183258, pay tribute to the teacher, hard

In order to facilitate learning and testing, all of the examples are created under the Oracle's own user Scott. The EMP table used,

If you do not have this table please refer to the article: copy in http://blog.csdn.net/xiaokui_wingfly/article/details/43957003
Now the customer's demand is the statistical Department of each job payroll, and finally need to count the total wages of all people, I believe this demand for everyone is relatively simple, and soon can write SQL statements, as follows:

  SELECT * FROM (select Deptno, Job, sum (SAL) from EMP Group by DEPTNO, job order by DEPTNO) UNION ALL  Select null Deptno , null job, SUM (SAL) from EMP;

The customer took a look at the head melon (as a watermelon shot), and then count the wages of each department. Tnnd, I add a union all to fix, the modified SQL statement is as follows:

----The payroll for each job in the statistics department, there is also a need to count the number of wages in each department and the total payroll for the statistical owner  SELECT * FROM (select Deptno, Job, sum (SAL) from the EMP group by DEPTNO, Jo b ORDER BY Deptno) UNION ALL  SELECT * FROM (select Deptno, null job, SUM (SAL) from EMP Group by Deptno ORDER by Deptno) UNION ALL  Select null Deptno, NULL job, SUM (SAL) from EMP;

In fact, there is a simple way to implement the following function GROUP by rollup (...)

Customer demand is like the kidney of the people of the same frequency of urine (who said the customer is God, God come so much demand?) ), and then count the payroll for each job type. Tnnd, I'll add a union all again, the modified SQL statement is as follows:

----The payroll for each job in the statistics department, it is also necessary to count the total wage of all persons and to count the number of salaries per department, and then to count the payroll for each job type  SELECT * FROM (select Deptno, Job, sum (SAL) from Emp
   group by Deptno, job order by DEPTNO) UNION ALL  SELECT * FROM (select Deptno, null job, SUM (SAL) from EMP Group by de Ptno ORDER by Deptno) UNION ALL  Select null deptno, Job, sum (SAL) from  EMP Group by jobunion all  select null de Ptno, NULL job, SUM (SAL) from EMP;

The above method can also be implemented using a simple function method, which modifies SQL as follows

Select grouping (Job), Deptno, Job, sum (SAL) from the EMP Group by cube (DEPTNO, job) order by Deptno; Select Deptno, Job, sum (SAL) from the EMP Group by cube (DEPTNO, job) order by Deptno; --Simplify the code above

Customers want to say, as long as the Statistics Department payroll and Work type of payroll can be (I wander every day in the killing and refrain from killing), I fucking lose a UNION All fix, the modified SQL statement is as follows:

--Statistical Work type payroll and departmental payroll  Select null deptno, Job, sum (SAL) from EMP Group by jobunion all  select Deptno, null job, Sum (SAL) from the EMP group by DEPTNO;

Here's another big trick, change the code as follows

Select grouping (Job), grouping_id (Job), grouping (DEPTNO), deptno, Job, sum (SAL) from EMP Group BY grouping sets (Deptno, Jo b);  

Busy half a day finally the customer needs are satisfied, a little relieved, but since the learning of new things, we need to finally summarize.

GROUP by ROLLUP (A,B,C): First Group by (A,B,C), then group by (a), then (a) group by, and finally group by operations on the whole table.
GROUP by CUBE (A,B,C): first Group by for (A,b,c), followed by (A, B), (A,c), (A), (B,c), (B), (C) to group by, and finally to the whole table group by operation.
GROUP BY GROUPING sets (A,b,c): in turn, group by for (C), (B), (A).

The detailed usage of these three functions reference article: http://www.2cto.com/database/201204/127014.html

Oracle Advanced Query GROUP BY