[SQL]关于Concatenated Groupings的问题[转载]

关于Concatenated Groupings的问题

SELECT department_id, job_id, manager_id, SUM(salary)
FROM employees
GROUP BY department_id,ROLLUP(job_id),CUBE(manager_id);

The example in the slide results in the following groupings:
• (department_id, manager_id, job_id )
• (department_id, manager_id)
• (department_id, job_id)
• (department_id)

解释：

首先看看SG上面的例子，
GROPU BY GROUPING SETS(a,b),GROPUING SETS(c,d)等价于：
GROUP BY (a,c)
UNION
GROUP BY (a,D)
UNION
GROUP BY (b,c)
UNION
GROUP BY (b,d)而rollup(job_id)=(job_id,null),cube(manager_id)=(manager_id,null)所以GROPU BY department_id,ROLLUP(job_id),CUBE(manager_id)就等价于deparment_id*(job_id,null)*(manager_id,null)最后的结果为：
(department_id,job_id,manager_id),(department_id,null,manager_id),(department_id,job_id,null),(department_id,null,null)=>
(department_id,job_id,manager_id),(department_id,manager_id),(department_id,job_id),(department_id);

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8558093/viewspace-1013928/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8558093/viewspace-1013928/