Teradata中ROLLUP和GROUPING的使用

例如：

SELECT 	department_number
	,SUM(salary_amount)
FROM employee
WHERE department_number < 402
GROUP BY ROLLUP (department_number)
ORDER BY 1;

 结果如下：

department_number  Sum(salary_amount)
-----------------------------  ------------------------------
               	   ?             	591925.00
              	100           	100000.00
              	201           	  73450.00
              	301           	116400.00
              	302            	  56500.00
              	401           	245575.00

 产生了一个department_number 为null的汇总记录。

2，为了区分那个是汇总那个是空值，我们可以根据下面的例子来做。

SELECT 
    CASE GROUPING (department_number) WHEN 1 THEN 'Total' ELSE department_number END AS Deptno
    ,SUM(salary_amount)
FROM employee
GROUP BY ROLLUP (department_number)
ORDER BY 1;

 结果如下：

  Deptno                     Sum(salary_amount)
   -----------              ------------------------------
          ?                  	                 60000.00
        100                         	100000.00
        201                       	  73450.00
        301                         	116400.00
        302                  	  56500.00
        401                         	245575.00
        402                  	  77000.00
        403                                  233000.00
        501                                  200125.00
       Total                                1162050.00

 综合运用

SELECT CASE GROUPING (department_number) WHEN 1 THEN 'Total Depts' 
	ELSE (COALESCE (department_number,'Null Dept') ) END AS Deptno
	,CASE GROUPING (job_code) WHEN 1 THEN 'All Job Codes' 
	ELSE (COALESCE (job_code,'Null Job Code') ) END AS job_code
	,SUM(salary_amount)
FROM employee
GROUP BY ROLLUP (department_number,job_code)
ORDER BY 1,2;

 结果如下

Deptno   	                   job_code   	   Sum(salary_amount) 
----------------  	-------------------  	 -----------------------------
403 		    431100 	            31200.00
403 		    432101 	            201800.00
403 	                All Job Codes                       233000.00
501 		    511100 	            66000.00
501 		    512101 	            134125.00
501 	                All Job Codes 	            200125.00
Null Dept 		All Job Codes 	            60000.00
Null Dept 		Null Job Code 	           60000.00
Total Depts 	All Job Codes 	           1162050.00

 生成逐层汇总的效果，很想CUBE吧，哈哈。