oracle dba 常用语句10（group by)

########################## 增强的 group by 子句 #########################select [column,] group_function(column)...from table[WHERE condition][GROUP BY [ROLLUP] group_by_expression][HAVING having_expression];[ORDER BY column]; -------ROLLUP 操作字，对 group by 子句的各字段从右到左进行再聚合example:/* 其结果看起来象对 col1 做小计 */select col1,col2,sum(col3) from table group by rollup(col1,col2);/* 复合 rollup 表达式 */select col1,col2,sum(col3) from table group by rollup((col1,col2));select [column,] group_function(column)...from table[WHERE condition][GROUP BY [CUBE] group_by_expression][HAVING having_expression];[ORDER BY column]; -------CUBE 操作字，除完成 ROLLUP 的功能外，再对 ROLLUP 后的结果集从右到左再聚合example:/* 其结果看起来象对 col1 做小计后，再对 col2 做小计，最后算总计 */select col1,col2,sum(col3) from table group by cube(col1,col2);/* 复合 rollup 表达式 */select col1,col2,sum(col3) from table group by cube((col1,col2));/* 混合 rollup,cube 表达式 */select col1,col2,col3,sum(col4) from table group by col1,rollup(col2),cube(col3);/*GROUPING(expr) 函数，查看 select 语句种以何字段聚合，其取值为 0 或 1*/select [column,] group_function(column)...,GROUPING(expr)from table[WHERE condition][GROUP BY [ROLLUP] group_by_expression][HAVING having_expression];[ORDER BY column];example:select col1,col2,sum(col3),grouping(col1),grouping(col2) from table group by cube(col1,col2);/*grouping sets 操作，对 group by 结果集先对 col1 求和，再对 col2 求和，最后将其结果集并在一起 */select col1,col2,sum(col3) from table group by grouping sets((col1),(col2));