数据库第07章数据分组与汇总.ppt

第7章数据分组与汇总 使用TOPn列出前n个记录使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句 使用TOPn列出前n个记录 关键字TOPn只列出结果集中前n个或前n 的记录 使用TOPn或TOPnPERCENT时 应注意在ORDERBY子句中指定值的范围关键字TOP后使用无符号的整数如果TOPnPERCENT生成小数 则SQLServer将把这个数取整可以在结果集中用WITHTIES子句包含那些值相等的记录 这时结果集中可以包含任意数目的行 使用TOPn列出前n个记录 续 统计orderdetails表中每个订单的总销售数量 将结果集按数量降序排列 并返回前10行 USEnorthwindSELECTTOP10orderid unitprice quantity astotalsaleFrom orderdetails Orderby unitprice quantity DESCGO 示例1 使用TOPn列出前n个记录 续 USEnorthwindSELECTTOP10WITHTIESorderid unitprice quantity astotalsaleFrom orderdetails Orderby unitprice quantity DESCGO 示例2 第7章数据分组与汇总 使用TOPn列出前n个记录使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句 使用聚合函数 常见的聚合函数及其描述 使用聚合函数 续 对包含空值的字段使用聚合函数时 应注意SQLServer的聚合函数 COUNT 除外 将忽略字段中的空值COUNT 将计算所有的行 即使每个字段都含有空值 使用聚合函数 续 查询titles表中类型是 popular comp 的书的平均价格 查询publishers表中供应商的数量 查询publishers表中供应商所在州的数量 selectavg price as 平均价格 fromtitleswheretype popular comp selectcount as 供应商数量 frompublishers selectcount state as 供应商所在州的数量 frompublishers 使用聚合函数 续 查询titles表中书的类型有几种 selectcount type fromtitles selectcount distincttype fromtitles 说明 允许count sum avg 和distinct一起处理列或表达式中不同的值 第7章数据分组与汇总 使用TOPn列出前n个记录使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句 GROUPBY的基础知识 使用GROUPBY子句联合使用GROUPBY子句和HAVING子句 GROUPBY的基础知识 简单分组按书的种类分类 求出各类书籍的数量 Selecttype count title as 数量 fromtitlesGroupbytype 使用GROUPBY子句 联合使用聚合函数和GROUPBY子句 能够把表中的记录分组 并对组中数据进行汇总 使用GROUPBY子句时 应注意对于指定的一组 SQLServer只生成一条记录 不返回详细信息不要对可包含空值的字段使用GROUPBY子句 因为空值也将被当作一组 使用GROUPBY子句 续 SELECTproductid SUM quantity AStotal quantityFROMorderhistGROUPBYproductidGO 使用GROUPBY子句 续 USEnorthwindSELECTproductid SUM quantity AStotal quantityFROMorderhistWHEREproductid 2GROUPBYproductidGO 使用GROUPBY子句 续 USEpubsSELECTroyalty AVG price 2 ASAveragePriceFROMpubs dbo titlesGROUPBYroyaltyGO 示例 对包含空值的字段使用GROUPBY子句 空值也将被当作一组 使用GROUPBY子句 续 按书的种类分类 求出3种类型书籍 business mod cook trad cook 的价格总和 平均价格以及各类书籍的数量 selecttype sum price astotal price avg price asavg price count title asquantityfromtitleswheretypein business mod cook trad cook groupbytype 使用GROUPBY子句 续 SELECT后面每一列 除了出现在统计函数中的列以外 都必须包含在在GROUPBY子句中 GROUPBY子句不支持对列分配的假名 也不支持任何使用统计函数的集合列 selectpub id type sum price astotal price avg price asavg price count title asquantityfromtitleswheretypein business mod cook trad cook groupbytype 使用GROUPBY子句 续 根据多列组合进行分组统计计算按书的类型分类 查询每个出版商销售的书的平均价和总销售额 selectpub id type sum price astotal price avg price asavg price count title asquantityfromtitlesgroupbypub id type 联合使用GROUPBY子句和HAVING子句 在分组的同时 对字段或表达式指定搜索条件使用HAVING子句时 应注意只在使用GROUPBY子句的同时 使用HAVING子句来限制分组可以引用任何出现在选择列表中的字段 联合使用GROUPBY子句和HAVING子句 续 SELECTproductid SUM quantity AStotal quantityFROMorderhistGROUPBYproductidHAVINGSUM quantity 30GO 列出表orderhist中销售量多于30的每种产品 selecttype avg price fromtitlesgroupbytypehavingavg price 15 selecttype avg price fromtitleswhereavg price 15groupbytype 联合使用GROUPBY子句和HAVING子句 续 按书的类型分类 查询平均价大于15的书的类型 平均价 第7章数据分组与汇总 使用TOPn列出前n个记录使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句 在结果集中生成汇总值 使用带有ROLLUP运算符的GROUPBY子句使用带有CUBE运算符的GROUPBY子句使用GROUPING函数 使用ROLLUP运算符 SELECTproductid orderid SUM quantity AStotal quantityFROMorderhistGROUPBYproductid orderidWITHROLLUPORDERBYproductid orderidGO 示例 使用ROLLUP运算符 续 说明GROUPBYA B CWITHROLLUP产生如下集合行的小计 A B C A B A 使用ROLLUP运算符 续 在生成包含小计和合计的报表时 ROLLUP运算符很有用 使用GROUPBY子句和ROLLUP操作符时 应注意SQLServer将在结果集中增加一行 这行将显示总和或平均值之类的汇总值 新增的行以NULL标识使用ROLLUP时 确保出现在GROUPBY后的各字段 在数据库环境中具有确定的 有意义的关系 使用CUBE运算符 CUBE操作符比ROLLUP操作符多产生两个汇总值 SELECTproductid orderid SUM quantity AStotal quantityFROMorderhistGROUPBYproductid orderidWITHCUBEORDERBYproductid orderidGO 描述 总数 对orderid1中的所有行汇总 对orderid2中的所有行汇总 只对productid1中的行汇总 只对productid2中的行汇总 只对productid3中的行汇总 示例 使用CUBE运算符 续 CUBE与ROLLUP的共同点都必须与GROUPBY子句联合使用 CUBE与ROLLUP的区别CUBE生成的结果集显示了所选列中值的所有组合的聚合ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合 使用GROUPING函数 区分结果集中的细节数据和汇总数据使用函数GROUPING时 应注意SQLServer将为GROUPING函数指定的字段生成一个新的字段如果SQLServer返回1 代表结果集中的这一记录是由ROLLUP或CUBE生成的SQLServer返回0 代表该记录原本就在数据库的表中出现在GROUPING函数中的字段也必须出现在GROUPBY子句中 1代表前一字段的汇总值0代表前一字段的具体值 使用GROUPING函数 续 SELECTproductid GROUPING productid orderid GROUPING orderid SUM quantity AStotal quantityFROMorderhistGROUPBYproductid orderidWITHCUBEORDERBYproductid orderidGO total quantity 示例 第7章数据分组与汇总 使用TOPn列出前n个记录使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句 使用COMPUTE和COMPUTEBY子句 生成某一列的明细值和汇总值的报表为组中的子集生成明细值和汇总值的报表 使用COMPUTE和COMPUTEBY子句 续 COMPUTE SELECTproductid orderid quantityFROMorderhistORDERBYproductid orderidCOMPUTESUM quantity GO 示例1 生成某一列的明细值和汇总值的报表 使用COMPUTE子句的注意事项和原则在一条语句中 可以同时使用多个COMPUTE和COMPUTEBY子句SQLServer中要求COMPUTE子句中的字段与选择列表中的字段相同 为组中的子集生成明细值和汇总值的报表 使用COMPUTEBY子句的注意事项和原则应当同时使用ORDERBY子句和COMPUTEBY子句 这样记录就会被分组显示出现在COMPUTEBY子句后的字段必须出现在ORDERBY子句后 且顺序相同 始于同一表达式 不能略过任一表达式可以同时产生多种汇总方式 随堂练习 SELECTproductid orderid quantityFROMorderhistORDERBYproductid orderidCOMPUTESUM quantity BYproductidGO SELECTproductid orderid quantityFROMorderhistORDERBYproductid orderidCOMPUTESUM quantity BYorderidGO 比较 回顾 学习完本章后 将能够 使用TOPn列出表中指定的前n个记录使用聚合函数生成单个汇总值使用聚合函数以及GROUPBY和HAVING子句 对某字段生成汇总值使用聚合函数以及GROUPBY和ROLLUP或CUBE操作符 为表生成汇总值使用COMPUTE和COMPUTEBY子句生成控制 中断报告 练习 请统计学期号为 第三学期 的每个学生所有科目的平均成绩现在想评定第二学年 包括第三 第四学期 的优秀学生奖学金 要求选出平均成绩排前5名的同学 考虑并列 前三学年 第一到第六学期 总平均分在90分以上而且没有一次考试不及格的同学的学号和总平均分 表名 SC