ORACLE提供了丰富的功能支持分组操作,包括聚集函数,GROUP BY子句,HAVING子句,以及GROUP BY子句功能的扩展--ROLLUP,CUBE和GROUPING SETS。
1.聚集函数语法:
aggregate_function([DISTINCT | ALL] expression)
The syntax elements are:
- aggregate_function
-
Gives the name of the function—e.g., SUM, COUNT, AVG, MAX, MIN
- DISTINCT
-
Specifies that the aggregate function should consider only distinct values of the argument expression.
- ALL
-
Specifies that the aggregate function should consider all values, including all duplicate values, of the argument expression. The default is ALL.
- expression
-
Specifies a column, or any other expression, on which you want to perform the aggregation.
注意当我们使用如下查询语句:
SELECT COUNT(*), COUNT(sale_price) FROM cust_order;
当sale_price列允许NULL时,我们可能会得到如下结果:
COUNT(*) COUNT(SALE_PRICE)
-------- -----------------
20 14
这是因为COUNT(sale_price)忽略NULL值造成的!
除了COUNT(*)之外,只有一个聚集函数(GROUPING)不忽略NULL值!
聚集函数中不允许使用DISTINCT的包括:
CORR, COVAR_POP, COVAR_SAMP, and all the linear regression functions
STTDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, and GROUPING
2.GROUP BY子句
当使用GROUP BY子句写一个查询语句时,有许多规则你需要知道。一般来说,任何在你select子句中的非聚集表达式必须出现在你的GROUP BY子句中。
- SELECT语句中同时使用聚集表达式和非聚集表达式一般需要一个GROUP BY子句
SELECT cust_nbr, sales_emp_id, COUNT(order_nbr)
FROM cust_order;
SELECT cust_nbr, sales_emp_id, COUNT(order_nbr)
*
ERROR at line 1:
ORA-00937: not a single-group group function
SELECT cust_nbr, sales_emp_id, COUNT(order_nbr)
FROM cust_order
GROUP BY cust_nbr;
SELECT cust_nbr, sales_emp_id, COUNT(order_nbr)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SELECT cust_nbr, COUNT(order_nbr)
FROM cust_order
GROUP BY cust_nbr, COUNT(order_nbr);
GROUP BY cust_nbr, COUNT(order_nbr)
*
ERROR at line 3:
ORA-00934: group function is not allowed here
SELECT 'CUSTOMER', cust_nbr, COUNT(order_nbr) FROM cust_order GROUP BY cust_nbr;
- 当标量函数被使用在SELECT列表中的某列,并不强迫你要在GROUP BY子句中包括这个标量函数
SELECT SUBSTR(lname,1,1), COUNT(*) FROM employee GROUP BY lname;
SELECT manager_emp_id || job_id, COUNT(*) FROM employee GROUP BY manager_emp_id || job_id;
SELECT manager_emp_id || job_id, COUNT(*) FROM employee GROUP BY manager_emp_id, job_id;
posted on 2007-08-09 17:53
tianjuchuan 阅读(1039)
评论(1) 编辑 收藏 引用 所属分类:
sql