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