资源描述：
《09 数据分组和汇总14755》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。
1、09数据分组和汇总14755--9****************************************************--数据分组和汇总--9.1----------------------------------------------------TOPn列出前几个纪录useNorthwindselectorderid,productid,quantityfrom[orderdetails]orderbyquantitydescselecttop5orderid,productid,q
2、uantityfrom[orderdetails]orderbyquantitydescselecttop5withtiesorderid,productid,quantityfrom[orderdetails]orderbyquantitydescselecttop5percentorderid,productid,quantityfrom[orderdetails]orderbyquantitydesc--9.2----------------------------------------------
3、------使用聚集函数--MIN(),MAX,AVG(),SUM(),COUNT(*)...第二部分已经讲过，不再重复--COUNT(*)和COUNT(column_name)的不同：COUNT(*)与一般聚集函数不同，其结果包含了NULL值selectEmployeeID,LastName,FirstName,Title,ReportsTofromemployeesselectcount(*)fromemployeesselectcount(reportsto)fromemployees--9.3---
4、-------------------------------------------------GROUPBY对指定的字段进行汇总--GROUPBY中使用WHERE子句，对汇总前的纪录进行筛选--GROUPBY中使用HAVING子句，对汇总后的纪录进行筛选--GROUPBY中使用ROLLUP,COMPUTE子句（不要求掌握）/*--创建样例数据createdatabaseTestUseTestcreatetableTest.dbo.orderhist(ProductIDint,OrderIDint,Qua
5、ntityint)insertdbo.orderhistvalues(1,1,5)insertdbo.orderhistvalues(1,2,10)insertdbo.orderhistvalues(2,1,10)insertdbo.orderhistvalues(2,2,25)insertdbo.orderhistvalues(2,3,20)insertdbo.orderhistvalues(3,1,15)insertdbo.orderhistvalues(3,2,30)*/selectproductid
6、,orderid,quantityfromorderhistselectproductid,sum(quantity)astotalfromorderhistgroupbyproductidselectproductid,orderid,quantityfromorderhistselectproductid,sum(quantity)astotalfromorderhistwhereproductid=2groupbyproductidselectproductid,orderid,quantityfro
7、morderhistselectproductid,sum(quantity)astotalfromorderhistgroupbyproductidhavingsum(quantity)>=30selectproductid,orderid,quantityfromorderhistselectproductid,orderid,sum(quantity)astotalfromorderhistgroupbyproductid,orderidwithrollupselectproductid,orderi
8、d,quantityfromorderhistselectproductid,orderid,quantityfromorderhistorderbyproductid,orderidcomputesum(quantity)selectproductid,orderid,quantityfromorderhistselectproductid,orderid,quantityfromorderhistorderb
