通過分組相關(guān)數(shù)據(jù)生成報(bào)告

目標(biāo)通過本章學(xué)習(xí)，您將可以:使用ROLLUP

操作分組使用CUBE操作分組使用GROUPING

函數(shù)處理ROLLUP

或CUBE操作所產(chǎn)生的空值使用GROUPINGSETS

操作進(jìn)行單獨(dú)分組組函數(shù)組函數(shù)處理多行返回一個(gè)行.舉例:SELECT [column,]group_function(column)...FROM table[WHERE condition][GROUPBY group_by_expression][ORDERBY column];SELECTAVG(salary),STDDEV(salary),COUNT(commission_pct),MAX(hire_date)FROMemployeesWHEREjob_idLIKE'SA%';GROUPBY

子句語法：例子:SELECTdepartment_id,job_id,SUM(salary),COUNT(employee_id)FROMemployeesGROUPBYdepartment_id,job_id;SELECT [column,]FROM table[WHERE condition][GROUPBY group_by_expression][ORDERBY column];group_function(column)...HAVING

子句使用HAVING

對組函數(shù)進(jìn)行限制.對查詢進(jìn)行第二次限制.SELECT [column,]group_function(column)...FROM table[WHERE condition][GROUPBY group_by_expression][HAVING having_expression] [ORDERBY column];帶有ROLLUP

和

CUBE

操作的GROUPBY

子句使用帶有ROLLUP

和CUBE

操作的GROUPBY

子句產(chǎn)生多種分組結(jié)果.ROLLUP

產(chǎn)生n+1種分組結(jié)果.CUBE

產(chǎn)生2的n次方種分組結(jié)果ROLLUP操作符ROLLUP

是對GROUPBY

子句的擴(kuò)展.ROLLUP

產(chǎn)生n+1種分組結(jié)果，順序是從右向左.SELECT [column,]group_function(column)...FROM table[WHERE condition][GROUPBY [ROLLUP]group_by_expression][HAVING having_expression];[ORDERBY column];ROLLUP

操作符:舉例

SELECTdepartment_id,job_id,SUM(salary)FROMemployeesWHEREdepartment_id<60GROUPBYROLLUP(department_id,job_id);312CUBE操作符CUBE是對GROUPBY

子句的擴(kuò)展.CUBE

會(huì)產(chǎn)生類似于笛卡爾集的分組結(jié)果.SELECT [column,]group_function(column)...FROM table[WHERE condition][GROUPBY [CUBE]group_by_expression][HAVING having_expression][ORDERBY column];CUBE

操作符:舉例SELECTdepartment_id,job_id,SUM(salary)FROMemployeesWHEREdepartment_id<60GROUPBYCUBE(department_id,job_id);1234GROUPING

函數(shù)GROUPING

函數(shù):GROUPING

函數(shù)可以和CUBE

或ROLLUP

結(jié)合使用使用GROUPING

函數(shù)，可以找到哪些列在該行中參加了分組使用GROUPING

函數(shù),可以區(qū)分空值產(chǎn)生的原因GROUPING

函數(shù)返回0或1SELECT[column,]group_function(column)..,

GROUPING(expr)FROM table[WHEREcondition][GROUPBY[ROLLUP][CUBE]group_by_expression][HAVINGhaving_expression][ORDERBYcolumn];GROUPING

函數(shù):舉例

SELECTdepartment_idDEPTID,job_idJOB,SUM(salary),GROUPING(department_id)GRP_DEPT,GROUPING(job_id)GRP_JOBFROMemployeesWHEREdepartment_id<50GROUPBYROLLUP(department_id,job_id);123GROUPING

SETS使用GROUPINGSETS

在同一個(gè)查詢中定義多個(gè)分組集.Oracle對GROUPINGSETS

子句指定的分組集進(jìn)行分組后用

UNIONALL

操作將各分組結(jié)果結(jié)合起來.Groupingset的優(yōu)點(diǎn):只進(jìn)行一次分組即可.不必書寫復(fù)雜的UNION

語句GROUPINGSETS

中包含的分組項(xiàng)越多,性能越好.NotesOnlyGROUPING

SETS:舉例SELECTdepartment_id,job_id,manager_id,avg(salary)FROMemployeesGROUPBYGROUPINGSETS

((department_id,job_id),(job_id,manager_id));…1…2復(fù)合列復(fù)合列是被作為整體處理的一組列的集合.ROLLUP

(a,

,

d)使用括號將若干列組成復(fù)合列在ROLLUP

或CUBE

中作為整體進(jìn)行操作.在ROLLUP

或CUBE中,復(fù)合列可以避免產(chǎn)生不必要的分組結(jié)果.(b,c)復(fù)合列：舉例SELECTdepartment_id,job_id,manager_id,SUM(salary)FROMemployees

GROUPBYROLLUP(department_id,(job_id,manager_id));…2341連接分組集連接分組集可以產(chǎn)生有用的對分組項(xiàng)的結(jié)合.將各分組集,ROLLUP和

CUBE

用逗號連接Oracle自動(dòng)在GROUPBY

子句中將各分組集進(jìn)行連接.連接的結(jié)果是對各分組生成笛卡爾集.GROUPBYGROUPINGSETS(a,b),GROUPINGSETS(c,d)………連接分組集:舉例SELECTdepartment_id,job_id,manager_id,SUM(salary)FROMemployees

GROUPBYdepartment_id,ROLLUP(job_id),CUBE(manager_id);12345總結(jié)通過本章學(xué)習(xí),您已經(jīng)可以:使用ROLLUP

操作符使用CUB