Oracle:grouping和rollup

標籤：

Oracle grouping和rollup簡單測試

SQL> select department_id,sum(salary) from employees where department_id in(10,30,90,100) group by department_id order by department_id;DEPARTMENT_ID SUM(SALARY)------------- -----------           10        4400           30       24900           90       58000          100       51608SQL> select department_id,first_name,sum(salary) from employees where department_id in(10,30,90,100) group by (department_id,first_name) order by department_id;DEPARTMENT_ID FIRST_NAME           SUM(SALARY)------------- -------------------- -----------           10 Jennifer                    4400           30 Alexander                   3100           30 Den                        11000           30 Guy                         2600           30 Karen                       2500           30 Shelli                      2900           30 Sigal                       2800           90 Lex                        17000           90 Neena                      17000           90 Steven                     24000          100 Daniel                      9000          100 Ismael                      7700          100 John                        8200          100 Jose Manuel                 7800          100 Luis                        6900          100 Nancy                      1200816 rows selectedSQL> select department_id,first_name,sum(salary) from employees where department_id in(10,30,90,100) group by rollup(department_id,first_name) order by department_id;DEPARTMENT_ID FIRST_NAME           SUM(SALARY)------------- -------------------- -----------           10 Jennifer                    4400           10                             4400           30 Alexander                   3100           30 Den                        11000           30 Guy                         2600           30 Karen                       2500           30 Shelli                      2900           30 Sigal                       2800           30                            24900           90 Lex                        17000           90 Neena                      17000           90 Steven                     24000           90                            58000          100 Daniel                      9000          100 Ismael                      7700          100 John                        8200          100 Jose Manuel                 7800          100 Luis                        6900          100 Nancy                      12008          100                            51608DEPARTMENT_ID FIRST_NAME           SUM(SALARY)------------- -------------------- -----------                                        13890821 rows selectedSQL> select department_id,grouping(department_id),first_name,grouping(first_name),sum(salary) from employees where department_id in(10,30,90,100) group by rollup(department_id,first_name) order by department_id;DEPARTMENT_ID GROUPING(DEPARTMENT_ID) FIRST_NAME           GROUPING(FIRST_NAME) SUM(SALARY)------------- ----------------------- -------------------- -------------------- -----------           10                       0 Jennifer                                0        4400           10                       0                                         1        4400           30                       0 Alexander                               0        3100           30                       0 Den                                     0       11000           30                       0 Guy                                     0        2600           30                       0 Karen                                   0        2500           30                       0 Shelli                                  0        2900           30                       0 Sigal                                   0        2800           30                       0                                         1       24900           90                       0 Lex                                     0       17000           90                       0 Neena                                   0       17000           90                       0 Steven                                  0       24000           90                       0                                         1       58000          100                       0 Daniel                                  0        9000          100                       0 Ismael                                  0        7700          100                       0 John                                    0        8200          100                       0 Jose Manuel                             0        7800          100                       0 Luis                                    0        6900          100                       0 Nancy                                   0       12008          100                       0                                         1       51608DEPARTMENT_ID GROUPING(DEPARTMENT_ID) FIRST_NAME           GROUPING(FIRST_NAME) SUM(SALARY)------------- ----------------------- -------------------- -------------------- -----------                                    1                                         1      13890821 rows selected

rollup為按分組統計小計和。
grouping(department_id)和grouping(first_name)
如果當前列所在的行為空白，則顯示為1，不為空白則顯示為0；

Oracle:grouping和rollup