目录

汇总数据

聚集函数

1.AVG()函数

2.COUNT()函数

3.MAX()函数

4.MIN()函数

5.SUM()函数

聚集不同值

组合聚集函数

分组函数

创建分组

过滤分组

分组和排序

select子句顺序

汇总数据

聚集函数

对某些行运行的函数，计算并返回一个值

1.AVG()函数

返回表中所有产品的平均价格

Select AVG(prod_price) as avg_price

From products;

返回表中特定产品的平均价格

Select AVG(prod_price) as avg_price

From products

Where vend_id=’DLL01’;

注意：1.AVG()函数只用于单个列

           2.该函数忽略列值为null的行

2.COUNT()函数

Select COUNT(*) as num_cust

From Customers;

注意：1.COUNT(*)对表中所有行的数目进行计数，不管是否为空值

           2.COUNT(column)对特定列进行计数，直接忽略NULL值

3.MAX()函数

Select MAX(prod_price) AS max_price

From Products;

注意：1.MAX()一般用于返回最大的数值或日期值；用于文本数据时，返回该列排序后的最后一行

           2.忽略NULL的行

4.MIN()函数

与MAX()功能相反，用于文本数据时，返回排序最前面的行

5.SUM()函数

用于统计某个物品的数量之和；也可用来合计计算值sum(item_price*quantity)

Select SUM(quantity) as items_ordered

From orderitems

Where order_num=20005;

聚集不同值

前文是对所有行执行计算，ALL参数为默认行为。只包含不同的值，指定DISTINCT参数

Select AVG(DISTINCT prod_price) as avg_price

From products

Where vend_id=’DLL01’;

注意：1.在Access中不支持DISTINCT

      2.DISTINCT不能用于COUNT(*)，且不能用于计算或表达式

      3.DISTINCT用于MIN()、MAX()没有价值

      4.除DISTINCT、ALL，还支持其他聚集函数，如：对查询结果的子集进行计算的TOP、TOP PERCENT

组合聚集函数

Select count(*) as num_items

     Min(prod_price) as price_min

     max(prod_price) as price_max

     avg(prod_price) as price_avg

From products;

分组函数

创建分组

Selcet vend_id,COUNT(*) as num_prods

From products

Group by vend_id;

 

Vend_id 为产品供应商的ID，num_prods为计算字段，group by按vend_id排序并分组数据

注意：1.group by可以包含任意数目的列。因而可以对分组进行嵌套，更细致地进行分组。若嵌套了分组，数据将在最后指定的分组上进行汇总

      2.group by子句中的每一列都必须是检索列或有效的表达式，不能是聚集函数。在select中使用表达式，则必须在group by指定相同的表达式，不能用别名

      3.大多数SQL不允许group by列带有长度可变的数据类型（如文本或备注型字段）

      4.除聚集计算语句，select中的每一列都必须在group by 中给出

      5.若有null值，则以null作为一个分组返回，若多行中有null值，则视它们为一组

      6.group by子句必须出现在where之后，order by之前

过滤分组

where过滤行，而having过滤分组；where在数据分组前进行过滤，having在数据分组后进行过滤。having支持所有的where操作符

Selcet vend_id,COUNT(*) as num_prods

From products

Group by vend_id

Having count(*)>=2;

也可同时使用where和having子句。返回具有两个以上产品且其价格大于等于4的供应商：

Selcet vend_id,COUNT(*) as num_prods

From products

Where prod_price>=4

Group by vend_id

Having count(*)>=2;

分组和排序

 

注意：一般使用group by子句时，应该也给出order by子句，这是保证数据正确排序的唯一方法，千万不要依赖group by排序数据

Select order_num,count(*) as items

From orderitems

Group by order_num

Having count（*）>=3

Order by items,order_num;

注意：access不允许别名排序，故上述代码在access失效。解决方法：order by count(*),order_num或者order by 2,order_num

select子句顺序