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语句的结果。 

    首先看一个例子          

     （1） SELECT cust_nbr, COUNT(order_nbr)

                FROM cust_order 
                GROUP BY cust_nbr
                HAVING cust_nbr < 6;

      （2）SELECT cust_nbr, COUNT(order_nbr)

                FROM cust_order 
                WHERE cust_nbr < 6
                GROUP BY cust_nbr; (推荐) 

      （3）SELECT cust_nbr, COUNT(order_nbr) 
                FROM cust_order 
                GROUP BY cust_nbr
                HAVING COUNT(order_nbr) > 2;(推荐)   

     WHERE在合计之前对表数据过滤，而HAVING在合计之后对组进行选择。（所以2，对非合计列过滤显然比较适合）

     直到GROUP BY语句执行完成，合计函数的结果才能确定。所以，对合计列过滤应该放在GROUP BY之后HAVING处进行。（所以3，比较适合，这也是在前面讨论GROUP BY和SELECT时候“合计表达式不允许在group by中使用”的原因）

     HAVING仅仅能后跟SELECT中选择的列，或者SELECT中不存在的合计表达式。如下：

          SELECT cust_nbr, COUNT(order_nbr) 
               FROM cust_order 
               GROUP BY cust_nbr
               HAVING order_dt < SYSDATE;（错误）

            SELECT cust_nbr
               FROM cust_order
               GROUP BY cust_nbr
               HAVING COUNT(order_nbr) < 5;（正确）
     HAVING同样不强制所用来过滤的列在SELECT中使用。

     HAVING和GROUP BY的顺序可以随意，推荐使用先GROUP BY后HAVING的格式。

     当WHERE、GROUP BY和HAVING同时使用的时候，执行顺序是先WHERE，后GROUP BY，最后使用HAVING。

(未完)