5排序,聚集与分组.doc

工程应用培训教材第五节 排序，聚集与分组应用背景数据库应用程序中，经常需要作报表打印功能，以满足管理统计方面的要求。针对数据表记录的排序，字段数据的求和,列表分组等等功能，SQL提供了功能强大的语句排序语句Order By，聚集语句Distinct，分组语句Group By，并且提供了功能强大的聚集函数Count(), Sum(), Avg(),Max (), Min()等，以便于对数据查询结果集进行统计分析。图5.1为某数据库应用管理信息系统开发过程中，典型的数据报表要求示例，要求将数据结果集输出到Excel报表中，然后由Excel来完成报表的打印。图5.1 典型的数据报表要求知识要点(1) 排序Order by子句SQL提供Order By子句对查询的结果进行排序，Order By 的基本格式如下：Select Select_list from Tblname where condition Order By column_list Asc | Desc关键字Order By一般放在Select语句的最后，按照Column_list列表顺序先后进行记录排序。关键字Asc 代表按照升序排序，是默认的排序方式，Desc代表按照降序排序。查询语句中如果没有Order By 子句，那么查询的结果按照数据的物理存储顺序排列。例1：以客户信息表为例，查询所有记录按客户代码以降序排序方式排列，Select语句如下：SELECT *FROM customerORDER BY cid DESC查询结果如下：mingren 韩先生 liubang 刘工 kh03 老刘 8299299213145760987郑州市建平街丙6号100083kh02 李经理 8679862113035760987成都市华西新村甲3号100083kh01 张经理 8865432113987760987成都市华西新村甲3号100083例2：按别名排序，以产品信息表为例，取产品名称列别名为s，然后按s升序排序，select语句如下：SELECT pcode, pname AS s, ptype, pbrand, punit, ppriceFROM productORDER BY s查询结果如下：005 birdbird007bird台 2100006 changhongchanghong988changhong台 2500001 motorolaV30moto台 2800003 nokiasony6110sony台 4500002 sonyC30moto台 3200004 sunsungsunsung510sunsung台 3100例3：多级排序，以产品信息表为例，取单价小于3000，品牌为中含有o的产品，按价格，品牌进行排序，Select语句如下：SELECT *FROM productWHERE (pprice 3000) AND (pbrand LIKE %o%)ORDER BY pprice, pbrand006 changhongchanghong988changhong台 2500001 motorolaV30moto台 2800(2) 聚集：Distinct Select子句中有两个关键字：All 与Distinct ,All为默认值，如果要在查询结果中去掉重复的记录，那么需要使用Distinct关键字，以删除重复的组合记录。例1：以产品信息表为例，列出品牌不可重复的品牌记录，Select语句如下：SELECT DISTINCT pbrandFROM product查询结果如下：birdchanghongmotosonysunsungDistinct语句的基本要求为：Distinct必须在Select_list列表之前使用，而不能在select_list的列表中使用；Distinct是对列的组合select_list进行组合去除的，如果没有重复的数据组合数据，则不去除记录。例2：以产品信息表为例，列出品牌与价格不重复的产品记录，Select语句如下：SELECT DISTINCT pbrand, ppriceFROM product查询结果如下，查询结果列出了品牌和价格组合没有重复的记录：bird2100changhong2500moto2800moto3200sony4500sunsung3100例3：由于空值是未知的，故无法与任何非空值作对比，彼此不等，但所选择的列中，如果出现空值，对于Distinct关键字则认为NULL值相等，例如查询客户信息表中，电话不相等的记录，Select语句如下：SELECT DISTINCT ctelFROM customer查询结果如下：829929928679862188654321(3)聚集函数Count(*):统计选择记录的个数，Count():统计特定列中值的不重复的个数。例1：查询客户信息表中的记录数，Select语句如下：SELECT COUNT(*) AS Expr1FROM customer查询结果为：5说明在客户信息表中共有5条记录。例2：查询客户信息表中，电话不重复的数量，Select语句如下：SELECT COUNT(ctel) AS Expr1FROM customer查询结果为：3说明电话不重复的记录有3个，结果中去除了电话为NULL的记录。Sum():计算特定列之和，Avg():计算特定列平均值。例3：查询产品信息表中所有产品购买4台后的价格之和，Select语句如下：SELECT SUM(pprice * 4) AS expr1FROM product查询结果如下：72800Max():确定特定列最大值；Min():确定特定列最小值。例4：查询产品信息表中，单价最高的价格，Select语句如下：SELECT MAX(pprice) AS expr1FROM product查询结果如下：4500(4) Group By分组子句在使用聚集函数时，有时希望一组数据使用聚集函数，例如我们在考虑销售单据的销售收入的时候，可能希望考核每个销售代表的销售收入状况，比如考核“Liuhong”的销售业绩，可以使用如下Select语句，如下：SELECT SUM(rmoney) AS Expr1FROM retailWHERE (remployee = liuhong)查询结果如下：4400而实际上，销售代表不仅仅有“liuhong”，还有“zhangling”等，那么如何进行销售数据的分别求和那？SQL给我们提供了Group By子句用于给记录分组，标准格式为：Group By group_list 关键字group by后边是分组字段列表，按照分组字段进行分组。例1：以销售单据为例，计算不同销售员的销售金额之和，select语句如下：SELECT SUM(rmoney) AS Expr1FROM retailGROUP BY remployee查询结果如下：44005000例2：以销售单据为例，计算不同客户代码的销售金额之和，select语句如下：SELECT SUM(rmoney) AS Expr1FROM retailGROUP BY cid查询结果如下：60003400例3：在不使用聚集函数时，Distinct与Group By类似，例如下列select语句的效果一致,如下：SELECT cidFROM retailGROUP BY cid与SELECT DISTINCT cidFROM retail查询结果都是：kh01 kh02 例4：多次分组，请对销售单据表按客户代码，销售代表进行多次分组，Select选择语句如下：SELECT cid, remployeeFROM retailGROUP BY cid, remployee查询结果如下：kh01 liuhong kh01 zhangling kh02 liuhong kh02 zhangling例5：Group By与Order By一起使用，查询到的数据首先按照Group By 进行分组，然后使用Order By进行排序，那么针对上面的销售单据问题，编写Select语句如下：SELECT cid, remployeeFROM retailGROUP BY cid, remployeeORDER BY remployee查询结果如下：kh01 liuhong kh02 liuhong kh01 zhangling kh02 zhangling(5)Having子句条件语句Hving子句，用于对选择列表的数据进行再次筛选，一般经常在Select语句中,与where子句，Group by子句联合使用。例1：在销售单据中，计算每个销售代表的销售单据数，且要求单据数小于2个单据的销售代表，select 语句如下：SELECT remployee, COUNT(*) AS Expr1FROM retailGROUP BY remployeeHAVING (COUNT(*) = 2)查询结果如下：kh01 3kh02 2应用举例：例1：在销售单据明细表中，添加记录如下：RidPcodedpricednumberdmoney11001 12002240021002 24002480031003 15001150042002 22002440052003 15002300063001 12002240073002 23002460083003 1500230001.请选择所有销售明细数据，并按照金额降序排序，select语句如下：SELECT *FROM detailORDER BY dmoney DESC查询结果如下：21002 24002480073002 23002460042002 22002440052003 15002300083003 15002300011001 12002240063001 12002240031003 1500115002.按销售的产品代码进行聚集，选择都有哪些产品在销售明细中有记录，select语句如下：SELECT DISTINCT pcodeFROM detailORDER BY pcode DESC查询结果如下：003 002 001 3按销售的产品代码进行聚集，计算每种销售产品的销售总金额，select语句如下：SELECT pcode, SUM(dmoney) AS Expr1FROM detailGROUP BY pcode查询结果如下：001 4800002 13800003 75004按销售单据号进行分组，计算每组的销售金额合计，Select语句如下：SELECT rid, SUM(dmoney) AS Expr1FROM detailGROUP BY rid查询结果如下：18700274003100005按产品分组，计算每组的销售金额，列出销售金额小于10000的产品，Select语句如下：SELECT pcode, SUM(dmoney) AS Expr1FROM detailGROUP BY pcodeHAVING (SUM(dmoney) = 10000)查询结果如下：001 4800003 7500知识扩展：(1)避免分组时产生错误。Group By使用起来很简单，但有时也存在一些潜在的问题，例如，按销售单据号、产品代码进行分组，则在列表中一定要有销售单据号、产品代码，否则和可能出现错误，正确的Select语句如下：SELECT rid, pcode, COUNT(*) AS Expr1FROM detailGROUP BY rid, pcode查询结果如下：1001 13001 11002 12002 13002 11003 12003 13003 1在使用Group By时应该注意的事项:(1).Group By,Having子句中的列表项必须在Select列表项中，否则产生错误(2).Order By子句必须跟在Group By后边，如果有Having子句，则在他们