Oracle公司内部数据库培训资料17对GROUPBY子句扩展.ppt

对 GROUP BY 子句的扩展 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 目标 通过本章学习，您将可以: 使用 ROLLUP 操作分组 使用 CUBE 操作分组 使用 GROUPING 函数处理 ROLLUP 或 CUBE操作所产生的空值 使用 GROUPING SETS 操作进行单独分组 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 组函数 组函数处理多行返回一个行 SELECT [column,] group_function(column). . . FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; SELECT AVG(salary), STDDEV(salary), COUNT(commission_pct),MAX(hire_date) FROM employees WHERE job_id LIKE SA%; 例子: Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. GROUP BY 子句 SELECT department_id, job_id, SUM(salary), COUNT(employee_id) FROM employees GROUP BY department_id, job_id ; SELECT [column,] FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; 例子: 语法: group_function(column). . . Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. HAVING 子句 使用 HAVING 对组函数进行限制 对查询进行第二次限制 SELECT [column,] group_function(column)... FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING having_expression] [ORDER BY column]; Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. 带有ROLLUP 和CUBE 操作的GROUP BY 子句 使用带有ROLLUP 和 CUBE 操作的GROUP BY 子句产生多种分组结果 ROLLUP 产生n + 1种分组结果 CUBE 产生2的n次方种分组结果 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd. ROLLUP 操作符 ROLLUP 是对 GROUP BY 子句的扩展 ROLLUP 产生n + 1种分组结果，顺序是从右向左 SELECT [column,] group_function(column). . . FROM table [WHERE condition] [GROUP BY [ROLLUP] group_by_expression] [HAVING having_expression]; [ORDER BY column]; Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2