38.Oracle数据库SQL开发之 对行进行分组

38.Oracle数据库SQL开发之 对行进行分组

欢迎转载，转载请标明出处： http://blog.csdn.net/notbaron/article/details/49723015      

  有时要把一个表中的行分为多个组，然后获得每个行组的一些信息。GROUP BY 子句可以将相似行分为一组。

1.       使用GROUP BY子句对行进行分组

GROUP BY子句可以将行分组为具有相同列值的多个部分。

store@PDB1>select product_type_id from products group by product_type_id;

 

PRODUCT_TYPE_ID

---------------

              1

 

              2

              4

              3

出现的空行是由product_type_id为空的那个行引起的。

去掉此行，执行如下：

1.1             分组中使用多列

执行如下：

store@PDB1>select product_id,customer_id from purchases group by product_id,customer_id;

PRODUCT_IDCUSTOMER_ID

---------------------

          1          1

          1          2

          1          3

          1          4

          2          1

          2          2

          2          3

          2          4

          3          3

1.2             对行组使用聚合函数

在行组中可以使用聚合函数。聚合函数读每组中的行进行计算，并未每个行组都返回一个值。

例如：

store@PDB1> select product_type_id,count(rowid)from products group by product_type_id order by product_type_id;

 

PRODUCT_TYPE_ID COUNT(ROWID)

--------------- ------------

               1             2

               2             4

               3             2

               4             3

                               1

store@PDB1> select variance(price) from productsgroup by product_type_id order by product_type_id;

 

VARIANCE(PRICE)

---------------

      50.50125

      280.8772

            .125

               7

               0

2.       调用聚合函数的错误用法

如果查询中包含一个聚合函数，而所选择的列并不在聚合函数中，那么这些列就必须在GROUP BY子句中。否则会出错，如下：

store@PDB1>select product_type_id,avg(price) from products;

selectproduct_type_id,avg(price) from products

       *

ERROR atline 1:

ORA-00937:not a single-group group function

所以，如果查询中包含聚合函数，而所选择的列并不在聚合函数中，那么这些列就必须在GROUP BY 子句中。

 

此外，不能在WHERE子句中使用聚合函数来限制行。否则会出错，如下：

store@PDB1>select product_type_id,avg(price) from products where avg(price)>20 group byproduct_type_id;

selectproduct_type_id,avg(price) from products where avg(price)>20 group byproduct_type_id

                                                     *

ERROR atline 1:

ORA-00934:group function is not allowed here

因为WHERE子句只能用来对单行而不是行组进行过滤。要过滤行组，可以使用HAVING子句。

3.       使用HAVING子句过滤行组

HAVING子句可以用于过滤行组，可以放在GROUP BY子句之后。

GROUP BY可以不与HAVING子句一起使用，但是HAVING必须与GROUP BY子句一起使用。

例如：

store@PDB1>select product_type_id,avg(price) from products group by product_type_id havingavg(price) > 20;

 

PRODUCT_TYPE_IDAVG(PRICE)

-------------------------

              1     24.975

              2      26.22

4.       组合使用WHERE和GROUPBY子句

WHERE和GROUP BY子句可以再同一个查询中一起使用。WHERE子句先对返回的行进行过滤，然后GORUP BY子句对保留的行进行分组。

例如：

store@PDB1>select product_type_id,avg(price) from products where price<15 group byproduct_type_id order by product_type_id;

 

PRODUCT_TYPE_ID AVG(PRICE)

--------------- ----------

               2     14.45

               3     13.24

               4     12.99

                        13.49

5.       组合使用WHERE、GROUPBY和HAVING子句

WHERE,GROUP BY和HAVING子句可以再同一查询中一起使用。

WHERE子句对表中的行进行过滤

GROUP BY子句对保留的行进行分组

HAVING子句对行组记性过滤

执行如下：

store@PDB1> select product_type_id,avg(price) fromproducts where price<15 group by product_type_id having avg(price) > 13order by product_type_id;

 

PRODUCT_TYPE_ID AVG(PRICE)

--------------- ----------

               2     14.45

               3     13.24

                        13.49