SQL第4章-数据统计.ppt

1,第4章数据操纵语句,4.9聚合函数4.10GROUPBY子句4.11HAVING子句4.12ROULLUP和CUBE运算符4.13COMPUTE和COMPUTEBY子句,2,4.9聚合(统计)函数,注意:除COUNT(*)外,其他统计函数都不统计列为NULL的记录信息,3,4.9聚合(统计)函数,例1：查询员工表employees中员工的个数。T-SQL语句如下:selectcount(*)fromemployees,注意：count(*)与count(列名)的区别,4,列的数据类型决定使用哪种函数：Count是仅有的能用于text、ntext或image数据类型的列的聚合函数。sum和avg仅能够用在数据类型的列中min和max不可以在bit数据类型的列中使用。,4.9聚合(统计)函数,5,同步任务,同步任务参见实训教程p95页同步任务1,6,4.9聚合(统计)函数,例2：查询products表中所有产品单价的平均值。语句如下:selectavg(unitprice)as平均值fromproducts,7,4.9聚合(统计)函数,例3：查询products表中类别编号categoryid为1的产品的平均单价值和总价格。语句如下:SELECTavg(unitprice)as平均单价值,sum(unitprice)as总价格FROMproductsWHEREcategoryid=1,注意:AVG(列名)函数在运算中不对值为NULL的进行计算,8,同步任务,同步任务参见实训教程p96页同步任务2-1p96页同步任务2-2,9,Groupby子句将查询结果集按一列或多列值分组，分组列值相等的为一组，并对每一组进行统计。语法格式为：GROUPBYALL列名WITHCUBE|ROLLUPHAVING筛选条件表达式,4.10GROUPBY子句,10,USEnorthwindSELECTproductid,orderid,quantityFROMorderdetailsGO,USEnorthwindSELECTproductid,SUM(quantity)AStotal_quantityFROMorderdetailsGROUPBYproductidGO,只对满足WHERE子句的行分组,USEnorthwindSELECTproductid,SUM(quantity)AStotal_quantityFROMorderdetailsWHEREproductid=2GROUPBYproductidGO,示例1,示例2,4.10GROUPBY子句,11,4.10GROUPBY子句,例4:统计“northwind”数据库“orders”表中每个城市的订单总数。T-SQL语句如下：Selectshipcity,count(orderid)as订单总数FromordersGroupbyshipcity,12,4.10GROUPBY子句,例5：查询products表中各种类别categoryid的产品的平均单价值和总价格。语句如下:SELECTcategoryid,avg(unitprice)as平均单价,sum(unitprice)as总价格FROMproductsgroupbycategoryid,13,例6：在“northwind”数据库“orders”表中按年份统计每个城市的订单总数。Selectshipcity,count(orderid)as订单总数，year(orderdate)as订购年份FromordersGroupbyshipcity,year(orderdate)Orderbyshipcity,year(orderdate),4.10GROUPBY子句,14,例7：在“Northwind”数据库“orders”表中统计1998年5月1日以来每个城市的订单总数及运费金额。其代码如下：SELECTshipcity,count(orderid)AS订单总数,sum(freight)AS运货费总数FROMordersWHEREorderdate1998-5-1GROUPBYshipcity如果使用Groupbyall的话，得出的结果将会不一样，其代码如下：SELECTshipcity,count(orderid)AS订单总数,sum(freight)AS运货费总数FROMordersWHEREorderdate1998-5-1GROUPBYALLshipcity,4.10GROUPBY子句,15,同步任务,同步任务参见实训教程p96页同步任务3-1p96页同步任务3-2,16,4.10GROUPBY子句,在使用GROUPBY子句时应注意:SELECT子句后只可以有两类表达式,即统计函数和进行分组的列名没有出现在GROUPBY子句后面的列名不能出现在SELECT子句中,但GROUPBY子句后面的列名可以不出现在SELECT子句如果使用了WHERE子句,那么所有参加分组计算的数据必须首先满足WHERE子句指定的条件默认情况下,GROUPBY子句按指定的列升序排列,17,4.10GROUPBY子句,练习：1、查询EMPLOYEES表中来源于每个城市的员工人数2、查询PRODUCTS表中来源于每个供应商的产品个数3、查询PRODUCTS表中来源于每个供应商每类产品的产品个数,18,例8：统计“northwind”数据库”orders”表中订单数超过20的城市，以及这些城市的总订单数和总运费。T-SQL语句如下：Selectshipcity,count(orderid)as订单总数,sum(freight)as运费总数FromordersGroupbyshipcityHavingcount(orderid)20,4.11HAVING子句,注意:WHERE子句不允许使用统计函数.,19,4.11HAVING子句,例9：查询orderdetails表中销售总数量值超过100的每个订单的订单号,销售总数量值。语句如下:SELECTorderid,sum(quantity)FROMorderdetailsGROUPBYorderidHAVINGsum(quantity)=100,20,同步任务,同步任务参见实训教程p97页同步任务4-1p97页同步任务4-2,21,4.11HAVING子句,例10：查询orderdetails表中定单号=10250的每个订单号,销售总数量值语句如下:SELECTorderid,sum(quantity)FROMorderdetailsWHEREorderid=10250GROUPBYorderid,SELECTorderid,sum(quantity)FROMorderdetailsGROUPBYorderidHAVINGorderid=10250,注意:HAVING子句中的列必须包含在聚合函数或GROUPBY子句中.,22,4.11HAVING子句,例11：查询orderdetails表中产品编号号=7的每个订单号,销售总数量值语句如下:SELECTorderid,sum(quantity)FROMorderdetailsWHEREproductid=7GROUPBYorderid,SELECTorderid,sum(quantity)FROMorderdetailsGROUPBYorderidHAVINGproductid=7,23,4.11HAVING子句,练习：1、查询ORDERDETAILS表中订购总数量超过50的产品编号及订购总数量,24,4.12ROULLUP和CUBE运算符,25,4.12ROULLUP和CUBE运算符,26,同步任务,同步任务参见实训教程p100页同步任务8,27,4.12ROULLUP和CUBE运算符,联合使用GROUPBY子句和ROLLUP操作符，计算组中的汇总值使用GROUPBY子句和ROLLUP操作符时，应注意处理GROUPBY中字段列表的顺序是从右到左，然后对每个组使用聚合函数将在结果集中增加一行，这行将显示总和或平均值之类的汇总值，新增的行以NULL标识,28,4.12ROULLUP和CUBE运算符,29,同步任务,同步任务参见实训教程p99页同步任务7,30,4.13COMPUTE与COMPUTEBY子句,Compute子句可对查询结果集中所有记录进行汇总统计，并显示所有参加汇总记录的详细信息。语法格式为：COMPUTE聚合函数(列名)BY列名,31,4.13COMPUTE和COMPUTEBY子句,32,例12：统计“northwind”数据库”orders”表中还没有发货的订单，以及运费的总数。T-SQL语句如下：Selectorderid,shipcity,freightFromordersWhereshippeddateisnullComputesum(freight),4.13COMPUTE与COMPUTEBY子句,33,例13：在“northwind”数据库”orders”表中按城市统计还没有发货的订单，以及运费的总数。T-SQL语句如下：Selectorderid,shipcity,freightFromordersWhereshippeddateisnullOrderbyshipcitydescComputesum(freight)byshipcity,4.13COMPUTE与COMPUTEBY子句,34,注意：computeby与groupby子句区别在于，前者既显示统计记录又显示详细记录,后者仅显示分组统计的汇总记录。compute子句指定的列要来自select选择列表中。compute