SQL第4章—数据统计.ppt

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