PLSQL的group操作
1,合计函数
aggregate_function([DISTINCT | ALL] expression)
空值与合计函数
SUM,MAX,MIN,AVG,COUNT都忽略空值,所以在对含有空值列求平均值或者合计总数时都要注意。
DISTINCT和ALL的使用
使用DISTINCT丢弃重复的表达式值,使用ALL正相反。合计函数使用丢弃后的表达式值计算,在默认情况下是ALL。
name job sal saldate
wang saleman 300 0824
li saleman 500 0823
wang saleman 500 0825
wang saleman 1200 0826
wang saleman 300 0827
这样查询select name,avg(sal), avg(distinct sal) from table group by name
输出:wang 575(2300/4) 666.6666667(2000/3)
2,GROUP BY语句
SELECT和GROUP BY的关系
select中的非合计表达式必须在group by中有所反映。
合计表达式不允许在group by中使用。
如果select中既有合计表达式又有非合计表达式,那么oracle会认为你要进行group操作,这个时候就需要group by语句。
仅当有且只有合计表达式的时候,group by语句可以省略,这时相当于在整个结果集上应用了合计表达式。
要在结果集中增加常量列,可以在group by增加该列描述,也可以不增加,结果是一样的。
如果scalar函数被应用到结果列中,oracle并不强迫在group byz中使用scalar函数。以下两个语句的执行结果是不相同的。
SELECT SUBSTR(lname,1,1), COUNT(*)
FROM employee
GROUP BY lname;
SELECT SUBSTR(lname,1,1), COUNT(*)
FROM employee
GROUP BY SUBSTR(lname,1,1);
链接函数在group by中有两种写法,如下。
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;
语法上没有强制被group by的字段一定在结果集中显示(被select出来),这和没有group by的情况下是一样的。但是为了使查询结果看起来更有意义,推荐不要省略这些字段。
当group by的列包含值null时,所有包含null值的行会被分配到同一个组中。这时可以使用order by nulls first/nulls last排列这些结果。
当需要对数据进行过滤和分组时可以一起使用where和group by,oracle会先过滤掉不符合where表达式的行,再进行分组。顺序是:
GROUP BY ......
WHERE ......
3,HAVING语句
WHERE、HAVING和GROUP BY
HAVING对GROUP BY的结果组进行过滤。当GROUP BY后面跟随HAVING时,返回的结果集将仅包含满足HAVING语句的结果。
首先看一个例子
GROUP BY cust_nbr
HAVING cust_nbr < 6;
WHERE cust_nbr < 6
GROUP BY cust_nbr; (推荐)
FROM cust_order
GROUP BY cust_nbr
HAVING COUNT(order_nbr) > 2;(推荐)
FROM cust_order
GROUP BY cust_nbr
HAVING order_dt < SYSDATE;(错误)
FROM cust_order
GROUP BY cust_nbr
HAVING COUNT(order_nbr) < 5;(正确)
HAVING同样不强制所用来过滤的列在SELECT中使用。