rollup
SELECT employee_id,department_id,job_id,SUM(salary)
FROM employees
WHERE department_id <60
GROUP BY ROLLUP (department_id,job_id,employee_id);
执行n+1次分组然后union，顺序是employee_id;job_id;department_id;空一共四次（从右至左每次以一个分组最后不分组求一次总和）
nothing
SELECT employee_id,department_id,job_id,SUM(salary)
FROM employees
WHERE department_id <60
GROUP BY department_id,job_id,employee_id;
按顺序检测，先按department_id分组计算然后一个部门的然后这个部门内job_id分组计算一次然后employee_id再分组计算一次，然后接着下一个部门分组……..执行下去 注:每次三个都同时被gruoping了
cube
SELECT employee_id,department_id,job_id,SUM(salary)
FROM employees
WHERE department_id <60
GROUP BY CUBE (department_id,job_id,employee_id);
执行2的n次方次分组计算，排列组合；一般用的时候都带order by排序确定谁先显示
Grouping
以使用Grouping 函数； 没有被Grouping到返回1，否则返回0
SELECT employee_id,department_id,job_id,SUM(salary),GROUPING(employee_id),GROUPING(department_id),GROUPING(job_id)
FROM employees
WHERE department_id < 60
GROUP BY department_id,job_id,employee_id;
SELECT employee_id,department_id,job_id,SUM(salary),GROUPING(employee_id),GROUPING(department_id),GROUPING(job_id)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP (department_id,job_id,employee_id);
SELECT employee_id,department_id,job_id,SUM(salary),GROUPING(employee_id),GROUPING(department_id),GROUPING(job_id)
FROM employees
WHERE department_id < 60
GROUP BY CUBE (department_id,job_id,employee_id);
GROUPING SETS
使用Grouping Set 来代替多次UNION
SELECT department_id,job_id,manager_id,AVG(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id,job_id),(job_id,manager_id));
相当于
SELECT department_id,job_id,manager_id,AVG(salary)
FROM employees
GROUP BY (department_id,job_id);
UNION
SELECT department_id,job_id,manager_id,AVG(salary)
FROM employees
GROUP BY GROUPING SETS (job_id,manager_id);
