用SELECT语句查询记录SQLServer程序设计语言

1.1使用SELECT语句检索数据1.1.1使用SELECT语句1.1.2指定列1.1.3使用WHERE子句指定行第1页/共88页第一页，共89页。SELECT[ALL|DISTINCT][TOPn]<选择列表>

[FROM]{<表资源>}[,…n]

[WHERE]<搜索条件>[GROUPBY]{<分组表达式>}[,…n][HAVING]<搜索条件>[ORDERBY]{<字段名[ASC|DESC]>}[,…n]基本语法使用SELECT语句用[]括起来的是可选项，SELECT是必需的选择列表指定了要返回的列WHERE子句指定限制查询的条件

在搜索条件中，可以使用比较操作符、字符串、逻辑操作符来限制返回的行数1.1.1使用SELECT语句第2页/共88页第二页，共89页。使用SELECT语句（续）FROM子句知道了返回的行和列所属的表DISTINCT选项从结果集中消除了重复的行，TOPn

选项限定了要返回的行数GROUPBY子句是对结果集进行分组HAVING子句是在分组的时候，对字段或表达式指定搜索条件ORDERBY子句对结果集按某种条件进行排序

1.1.1使用SELECT语句第3页/共88页第三页，共89页。指定列1.1.2指定列在选择列表中指定要从表中返回的列在选择列表中可以包含字段名、表达式、所选关键字或要赋值的局部变量。其语法为<选择列表>::=

{*|{表名|视图名|表的别名}.*

|{字段名|表达式

|$IDENTITY|$ROWGUID}

[[AS]字段别名]

|字段别名=表达式

}[,...n]指定要检索的字段时，请注意选择列表将按照指定顺序查找并显示所查询的字段不同的字段名用“，”分开在选择列表中，避免或尽量少使用符号“*”。该符号代表选择表中所有的字段第4页/共88页第四页，共89页。使用WHERE子句指定行1.1.3使用WHERE子句指定行通过使用WHERE子句，可以得到基于搜索条件的行<搜索条件>::=

{[NOT]<断言>|(<搜索条件>)}

[{AND|OR}[NOT]{<断言>|(<搜索条件>)}]

}[,...n]<断言>列出了可以在WHERE子句中包含的表达式列表使用WHERE子句指定行时，请注意在所有数据类型为char、nchar、varchar、nvarchar、text、datetime和smalldatetime的数据周围，必须使用单引号（‘）在使用SELECT语句时，尽量使用WHERE语句限制将要返回的行第5页/共88页第五页，共89页。使用WHERE子句指定行（续）employeeidlastnamefirstnametitle5BuchananStevenSalesManagerUSEnorthwindSELECTemployeeid,lastname,firstname,titleFROMemployeesWHEREemployeeid=5GO示例1.1.3使用WHERE子句指定行第6页/共88页第六页，共89页。1、检索数据1.1使用SELECT语句检索数据1.2过滤数据1.3设置结果集格式1.4需要考虑的性能问题1.5推荐操作第7页/共88页第七页，共89页。过滤数据1.2过滤数据过滤的类型搜索条件使用比较运算符＝、>、<、>=、<=、<>使用字符串比较符LIKE和NOTLIKE使用逻辑运算符AND、OR检索一定范围内的值BETWEEN和NOTBETWEEN使用值列表作为搜索条件IN和NOTIN检索未知值ISNULL和ISNOTNULL第8页/共88页第八页，共89页。使用比较运算符1.2.1使用比较运算符使用比较操作符来比较表中的值与指定的值或表达式的值USEnorthwindSELECTlastname,cityFROMemployeesWHEREcountry='USA'GO第9页/共88页第九页，共89页。使用字符串比较符1.2.2使用字符串比较符在比较字符串时，可以使用LIKE和通配符来查找需要的行通配符的种类通配符描述%0或多个字符串_任何单个的字符[]在指定区域或集合内的任何单个字符[^]不在指定区域或集合内的任何单个字符第10页/共88页第十页，共89页。使用字符串比较符（续）1.2.2使用字符串比较符表达式LIKE'%EEN'LIKE'_EEN'LIKE'[CK]'LIKE'[S-V]'LIKE'99[0-9][0-9]'LIKE'[A-Z][0-9][FM]'LIKE'M[^C]'LIKE'5_\%'ESCAPE'\'LIKE'A\[B\][A-Z]'ESCAPE'\'第11页/共88页第十一页，共89页。使用逻辑运算符1.2.3使用逻辑运算符用逻辑操作符AND、OR和NOT

来连接一系列的表达式，及优化查询过程。使用逻辑运算符时，遵循下列原则使用AND返回满足所有条件的行使用OR返回满足任一条件的行使用NOT返回不满足条件的行有多个表达式时，使用圆括号为表达式分组改变表达式求值顺序增加表达式的可读性第12页/共88页第十二页，共89页。使用逻辑运算符（续）搜索条件的顺序MicrosoftSQLServer首先求NOT表达式的值，然后是AND，最后是OR当表达式中所有的操作符优先级相同时，求值顺序由左到右1.2.3使用逻辑运算符(productnameLIKE'T%'ORproductid=46)

AND(unitprice>16.00)(productnameLIKE'T%')OR(productid=46ANDunitprice>16.00)

第13页/共88页第十三页，共89页。检索一定范围内的值使用BETWEEN

来查询在一定范围内的值，使用BETWEEN时，注意SQLServer返回的结果集中，包含范围内的边缘值尽量使用BETWEEN，而不用AND和比较操作符表示的表达式（>=xAND<=y）。要使结果集不包含边缘值，则使用（>xAND<y）要返回不在指定区域的行时，使用NOTBETWEEN1.2.4检索一定范围内的值unitpriceBETWEEN10AND20第14页/共88页第十四页，共89页。使用值列表作为搜索条件1.2.5使用值列表作为搜索条件使用IN语句指定一个值的列表作为查询条件。使用IN时，注意使用IN搜索条件和使用由OR操作符连起来的一系列比较操作符，其效果是等价的在搜索条件中不能包含NULL值，这将返回不可预测的结果集使用NOTIN搜索条件来返回值不在指定列表中的行。使用NOT会降低数据检索速度countryIN('Japan','Italy')country='Japan‘orcountry='Italy'第15页/共88页第十五页，共89页。检索未知值1.2.6检索未知值输入数据时，没有指定一个字段的值，且此字段没有定义默认值的值，称此字段含有空值（NULL）使用ISNULL搜索条件来查询某指定列没任何信息的行。需要返回含有空值的行时，注意把空值和任何表达式比较都会失败，因为空值不等于任何表达式在CREATETABLE语句里，定义一个字段是否允许空值的存在使用ISNOTNULL来查询指定列中非空的行ScoreISNULL第16页/共88页第十六页，共89页。1、检索数据1.1使用SELECT语句检索数据1.2过滤数据1.3设置结果集格式1.4需要考虑的性能问题1.5推荐操作第17页/共88页第十七页，共89页。设置结果集格式对数据进行排序消除重复行改变列名使用字面值FOR子句UNION[ALL]1.3设置结果集格式第18页/共88页第十八页，共89页。对数据进行排序1.3.1对数据进行排序使用ORDERBY子句为结果集中的行排序时，有升序和降序两种。使用ORDERBY时，注意除非使用ORDERBY指定顺序，SQLServer并不主动为结果集中的行排序SQLServer缺省地按升序排列ORDERBY子句中指定的列并不一定要出现在选择列表中可以按照字段名、计算出的值或表达式进行排序可以在ORDERBY子句中引用某字段在选择列表中的位置而进行排序ORDERBY子句不能用于数据类型text、ntext或image字段第19页/共88页第十九页，共89页。对数据进行排序（续）USEnorthwindSELECTproductid,productname,categoryid,unitprice

FROMproducts

ORDERBYcategoryid,unitpriceDESCGOproductidproductnamecategoryidunitprice38CotedeBlaye1263.500043IpohCoffee146.00002Chang119.0000…………63Vegie-spread243.90008NorthwoodsCranberrySauce240.000061Siropd'érable228.5000…………示例1.3.1对数据进行排序第20页/共88页第二十页，共89页。消除重复行1.3.2消除重复行用DISTINCT可以消除重复行，使结果集列出惟一的值。使用DISTINCT时，注意除非指定DISTINCT子句，否则SELECT语句将返回所有满足搜索条件的行选择列表中所有列的组合值将决定其惟一性包含任何惟一组合值的行都将被检索到并被返回到结果集中除非使用了ORDERBY子句，否则DISTINCT子句将按随机的顺序显示结果集中的行第21页/共88页第二十一页，共89页。消除重复行（续）USEnorthwindSELECTDISTINCTcountry

FROMsuppliers

ORDERBYcountryGOcountryAustraliaBrazilCanadaDenmarkFinlandFranceGermanyItalyJapanNetherlandsNorwaySingaporeSpainSwedenUKUSA示例1.3.2消除重复行第22页/共88页第二十二页，共89页。改变列名1.3.3改变列名为了提高可读性，可以为选择列表中的字段名起别名改变字段名时，应注意缺省情况下，结果集所显示的字段名，就是在CREATETABLE语句中指定的字段名如果字段名中有空格，或字段名不符合SQLServer命名规则，用单引号来标识这个字段名可以为经过计算的包含函数、字符符号的字段起别名字段名中至多可以包含128个字符第23页/共88页第二十三页，共89页。改变列名（续）USEnorthwindSELECTfirstnameASFirst,lastnameLast ,'EmployeeID:'=employeeidFROMemployeesFirstLastEmployeeID:NancyDavolio1AndrewFuller2JanetLeverling3MargaretPeacock4StevenBuchanan5MichaelSuyama6RobertKing7LauraCallahan8AnneDodsworth91.3.3改变列名示例第24页/共88页第二十四页，共89页。使用字面值1.3.4使用字面值使用字面值可以增加结果集的可读性，符号可能是字母、数字或标识，它们被用作特定的值。其语法为SELECT字段名

|’字符串符号’

[，字段名

|’字符串符号’…]

FROM表名USEnorthwindSELECT

firstname,lastname ,'Identificationnumber:',employeeidFROMemployees

GO第25页/共88页第二十五页，共89页。FOR子句FOR子句用来指定BROWSE或XML选项，主要在应用程序的开发中经常使用。FOR子句生成XML的详细使用介绍请参考第24章。本节通过一个使用FOR子句的示例，简单说明其一般的功能。1.3.5FOR子句USEnorthwindSELECTcountryFROMsuppliersORDERBYcountryforxmlautoGO示例第26页/共88页第二十六页，共89页。UNION[ALL]将两个或更多查询的结果合并为单个结果集，该结果集包含联合查询中的所有查询的全部行。UNION运算不同于使用联接合并两个表中的列的运算。1.3.6UNION[ALL]USEAdventureWorksSELECTProductModelID,NameFROMProduction.ProductModelUNIONSELECTProductModelID,NameFROMdbo.ProductModel2ORDERBYName;使用UNION合并两个查询结果集的基本规则：所有查询中的列数和列的顺序必须相同。数据类型必须兼容。第27页/共88页第二十七页，共89页。需要考虑的性能问题非逻辑操作会降低查询速度

比如：NOTBETWEEN、NOTIN和ISNOTNULL

LIKE搜索条件会降低查询速度使用精确查询或在一定范围的查询可能会提高数据查询速度ORDERBY子句可能降低数据查询速度

因为SQLServer在返回第一行前，必须对结果集中所有的行进行计算并排序1.4需要考虑的性能问题第28页/共88页第二十八页，共89页。推荐操作使用DISTINCT子句消除结果集中重复的行通过改变字段名和使用字面值来提高结果集的可读性第29页/共88页第二十九页，共89页。第30页/共88页第三十页，共89页。提出问题我们遇到了下面的问题：我们拥有桌子和椅子两种产品，每种产品都有红色和蓝色两种颜色。每种颜色的桌子有多少库存，每种颜色的椅子有多少库存。椅子有多少库存，不论它是什么颜色；桌子有多少库存，不论它是什么颜色。红色的商品一共有多少库存，蓝色的商品一共有多少库存。这个时候大家发现，我们需要的是以不同商品种类和颜色进行排列组合的小计报表。第31页/共88页第三十一页，共89页。8.2、数据分组与汇总使用TOPn列出前n个记录使用TABLESAMPLE限制结果集使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句推荐操作第32页/共88页第三十二页，共89页。使用TOPn

列出前

n

个记录8.2.1使用TOPn列出前n个记录关键字TOPn

只列出结果集中前n

个或前n%的记录。使用TOPn

或TOPnPERCENT时，应注意在ORDERBY子句中指定值的范围关键字TOP后使用无符号的整数如果TOPnPERCENT生成小数，则SQLServer将把这个数取整可以在结果集中用WITHTIES子句包含那些值相等的记录，这时结果集中可以包含任意数目的行部分语法：SELECT[ALL|DISTINCT]

[TOPn[PERCENT][WITHTIES]]

<select_list>……第33页/共88页第三十三页，共89页。要求：1、检索出SC表中的所有成绩记录2、检索出SC表中的前5条成绩记录3、检索出SC表中的前50%的成绩记录4、检索出SC表中的前8%的成绩记录。注意观察有几条记录? 5、检索出SC表中成绩前5名的学生学号1、select*fromsc使用TOPn

列出前

n

个记录8.2.1使用TOPn列出前n个记录2、selecttop5*

fromsc3、selecttop50percent*fromsc4、selecttop8percent*fromscselect

top5withties*fromscorderbyscoredescselect

top5*fromscorderbyscoredesc第34页/共88页第三十四页，共89页。使用TOPn

列出前

n

个记录8.2.1使用TOPn列出前n个记录限制结果集大小的另一种方法是在执行一个语句之前执行SETROWCOUNTn语句。SETROWCOUNT与TOP的不同之处在于：TOP子句适用于指定了该子句的单个SELECT语句。在执行另一个SETROWCOUNT语句之前，SETROWCOUNT会一直有效，例如执行SETROWCOUNT0将会关闭此选项。SETROWCOUNTn第35页/共88页第三十五页，共89页。使用TOPn列出前

n

个记录使用TABLESAMPLE限制结果集使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句推荐操作8.2、数据分组与汇总第36页/共88页第三十六页，共89页。使用TABLESAMPLE限制结果集8.2.2使用TABLESAMPLE限制结果集

TABLESAMPLE子句将从FROM子句中的表返回的行数限制到样本数或行数的某一百分比。不能对视图使用TABLESAMPLE。部分语法：TABLESAMPLE[SYSTEM](sample_number[PERCENT|ROWS])第37页/共88页第三十七页，共89页。--执行几次代码，所得到的结果是不同的USEAdventureWorks;GOSELECTcount(*)FROMSales.CustomerTABLESAMPLESYSTEM(10PERCENT);使用TABLESAMPLE限制结果集第38页/共88页第三十八页，共89页。使用TOPn列出前

n

个记录使用TABLESAMPLE限制结果集使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句推荐操作8.2、数据分组与汇总第39页/共88页第三十九页，共89页。使用聚合函数（续）8.2.3使用聚合函数计算诸如平均值和总和的函数称为聚合函数SQLServer对整个表或表里某个组中的字段进行汇总、计算，然后生成单个的值可以在SELECT语句中单独使用聚合函数，也可以与语句GROUPBY联合使用除了COUNT函数，如果记录集中没有满足WHERE子句的记录，则所有函数返回空值，COUNT返回0字段的数据类型决定了可以用在该字段上的聚合函数类型如：SUM和AVG只能用在数据类型代表数字的字段上第40页/共88页第四十页，共89页。SUM和AVG。SUM和AVG分别用于求表达式中所有值项的总和与平均值。语法格式：

SUM|AVG(ALL|DISTINCT]expression)SUM|AVG忽略空值。使用聚合函数（续）8.2.3使用聚合函数第41页/共88页第四十一页，共89页。使用聚合函数（续）MAX和MIN。MAX和MIN分别用于求表达式中所有值项的最大值和最小值。语法格式：

MAX|MIN([ALL|DISTINCT]expression)

只能用在数字、字符、和时间日期数据类型。MAX|MIN忽略空值。8.2.3使用聚合函数第42页/共88页第四十二页，共89页。COUNT与COUNT(*)。用于统计组中满足条件的行数或总行数。语法格式：

COUNT({[ALL|DISTINCT]expression}|*)

选择*时将统计总行数。COUNT忽略空值，

COUNT(*)计算空值。使用聚合函数（续）8.2.3使用聚合函数第43页/共88页第四十三页，共89页。使用含有空值的聚合函数空值能使聚合函数产生意想不到的结果对包含空值的字段使用聚合函数时，应注意SQLServer的聚合函数（COUNT(*)除外）将忽略字段中的空值COUNT（*）将计算所有的行，即使每个字段都含有空值使用含有空值的聚合函数第44页/共88页第四十四页，共89页。createtabletable1(snochar(5),agetinyint)insertintotable1values('95001',null)insertintotable1values('95002',null)insertintotable1values(null,null)insertintotable1values('95003',null)--以下返回的结果分别是什么selectcount(*)fromtable1selectcount(sno)fromtable1selectcount(age)fromtable1selectavg(age)fromtable1第45页/共88页第四十五页，共89页。ISNULL

如果第一个表达式为非NULL，返回第一个表达式，否则返回第二个表达式ISNULL()的使用ISNULL(表达式1，表达式2)第46页/共88页第四十六页，共89页。ISNULL()的使用

selectavg(salary)fromavg_testselectavg(isnull(salary,0))fromavg_test第47页/共88页第四十七页，共89页。常见的聚合函数及其描述聚合函数描述AVG 返回组中值的平均值。空值将被忽略。COUNT 表达式中值的数目COUNT(*)所选择的行的数目MAX返回表达式的最大值。MAX忽略任何空值。对于字符列，MAX查找排序序列的最大值。MIN返回表达式的最小值。MIN忽略任何空值。对于字符列，MIN查找排序序列的最低值。SUM返回表达式中所有值的和，或只返回DISTINCT值。SUM只能用于数字列。空值将被忽略。GROUPING是一个聚合函数,它产生一个附加的列，当用CUBE或ROLLUP运算符添加行时，附加的列输出值为1，当所添加的行不是由CUBE或ROLLUP产生时，附加列值为0。仅在与包含CUBE或ROLLUP运算符的GROUPBY子句相联系的选择列表中才允许分组。分组用于区分由CUBE和ROLLUP返回的空值和标准的空值。作为CUBE或ROLLUP操作结果返回的NULL是NULL的特殊应用。它在结果集内作为列的占位符，意思是"全体"。第48页/共88页第四十八页，共89页。使用TOPn列出前

n个记录使用TABLESAMPLE限制结果集使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句推荐操作8.2、数据分组与汇总第49页/共88页第四十九页，共89页。GROUPBY的基础知识使用GROUPBY子句联合使用GROUPBY子句和HAVING子句8.2.4GROUPBY的基础知识第50页/共88页第五十页，共89页。联合使用聚合函数和GROUPBY子句，能够把表中的数据按字段分组，并对组中数据进行汇总。语法：

GROUPBY[ALL]group_by_expression[,…n][WITH]{CUBE|ROLLUP}]]group_by_expression 用于分组的表达式，WITH指定CUBE或ROLLUP操作符。GROUPBY的基础知识8.2.4GROUPBY的基础知识第51页/共88页第五十一页，共89页。USEnorthwindSELECTproductid,orderid

,quantityFROMorderhist

GOproductidtotal_quantity115235345productidorderidquantity11512102110222531153230productidtotal_quantity235只对满足WHERE子句的行分组USEnorthwindSELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistWHEREproductid=2GROUPBYproductid

GO示例2USEnorthwindSELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductid

GO示例1GROUPBY的基础知识8.2.4GROUPBY的基础知识第52页/共88页第五十二页，共89页。使用GROUPBY子句时，应注意SQLServer将为每一组计算一个汇总值，并把汇总值保存在一个字段中对于指定的一组，SQLServer只生成一条记录，不返回详细信息SQLServer只对满足WHERE子句的记录进行分组和汇总不要对可包含空值的字段使用GROUPBY子句，因为空值也将被当作一组联合使用关键字ALL和GROUPBY子句时，组合字段中含有空值的行都将被列出，而不管记录是否满足WHERE子句的条件使用GROUPBY子句使用GROUPBY子句第53页/共88页第五十三页，共89页。--下面的例子给出了针对所有type和pub_id的唯一组合进行分类并统计计算的情况。usepubsgoselecttype,pub_id,avg(price)'avg_price',sum(price)'sum_price'fromtitleswheretypein('business','trad_cook')groupbyalltype,pub_idorderbytype第54页/共88页第五十四页，共89页。createtablep1(snochar(3),sexchar(8),ageint)insertintop1values('001','m',20)insertintop1values(null,null,null)insertintop1values(null,null,null)selectcount(sno)fromp1groupbysno不要对可包含空值的字段使用GROUPBY子句，因为空值也将被当作一组第55页/共88页第五十五页，共89页。使用GROUPBY子句（续）SELECTproductid,orderid

,quantityFROMorderhist

GOSELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductid

GOproductidtotal_quantity115235345productidorderidquantity11511102110222531153230productidtotal_quantity235只对满足WHERE子句的行分组SELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistWHEREproductid=2GROUPBYproductid

GO示例1示例使用GROUPBY子句第56页/共88页第五十六页，共89页。联合使用GROUPBY子句和HAVING子句联合使用GROUPBY子句和HAVING子句在分组的同时，对字段或表达式指定搜索条件使用HAVING子句时，应注意只在使用GROUPBY子句的同时，使用HAVING子句来限制分组可以引用任何出现在选择列表中的字段不要联合使用关键字ALL和HAVING子句。因为HAVING子句会忽略ALL关键字，并返回只符合HAVING条件的组第57页/共88页第五十七页，共89页。联合使用GROUPBY子句和HAVING子句（续）SELECTproductid,orderid,quantityFROMorderhist

GOSELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductidHAVINGSUM(quantity)>=30

GOproductidtotal_quantity235345productidorderidquantity11512102110222531153230联合使用GROUPBY子句和HAVING子句第58页/共88页第五十八页，共89页。WHERE子句与HAVING子句的区别：

WHERE子句作用于基本表或视图，从中选择满足条件的记录；

HAVING子句作用于组，从中选择满足条件的组联合使用GROUPBY子句和HAVING子句（续）联合使用GROUPBY子句和HAVING子句第59页/共88页第五十九页，共89页。使用TOPn列出前n

个记录使用TABLESAMPLE限制结果集使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句推荐操作8.2、数据分组与汇总第60页/共88页第六十页，共89页。在结果集中生成汇总值使用带有CUBE运算符的GROUPBY子句使用带有ROLLUP运算符的GROUPBY子句使用GROUPING函数8.2.5在结果集中生成汇总值第61页/共88页第六十一页，共89页。使用带有CUBE运算符的GROUPBY子句使用带有CUBE运算符的GROUPBY子句使用带有CUBE运算符的GROUPBY子句创建并汇总所有可能的基于GROUPBY子句分组的组合。使用带有CUBE运算符的GROUPBY子句用于提供标准的关系型格式的数据。第62页/共88页第六十二页，共89页。selectitem,color,sum(quantity)fromcpgroupbyitem,colorwithcubechair、blue的详细值chair、red的详细值汇总chair的所有记录table、blue的详细值table、red的详细值汇总table的所有记录总计汇总bule的所有记录汇总red的所有记录每种产品的每种颜色的总数量，每种产品的所有颜色的总数量，每种颜色的所有产品的总数量，所有产品所有颜色的总数量。第63页/共88页第六十三页，共89页。使用带有CUBE运算符的GROUPBY子句使用带有CUBE运算符的GROUPBY子句使用GROUPBY子句和CUBE操作符时，应注意如果在GROUPBY子句中有

n个字段或表达式，SQLServer将在结果集中返回2n种可能的组合结果集中含有NULL的记录代表该记录由CUBE操作符生成（注意此NULL不是基本表中的实际空值）不能同时使用关键字ALL和操作符CUBE第64页/共88页第六十四页，共89页。使用带有ROLLUP运算符的GROUPBY子句使用带有

ROLLUP

运算符的

GROUPBY

子句ROLLUP运算符的功能类似于CUBE运算符。使用带有ROLLUP运算符的GROUPBY子句汇总分组值使用带有ROLLUP运算符的GROUPBY子句用于提供标准的关系型格式的数据。第65页/共88页第六十五页，共89页。selectitem,color,sum(quantity)fromcpgroupbyitem,colorwithrollupchair、blue的详细值chair、red的详细值汇总chair的所有记录table、blue的详细值table、red的详细值汇总table的所有记录总计每种产品的每种颜色的总数量，每种产品的所有颜色的总数量，所有产品所有颜色的总数量。第66页/共88页第六十六页，共89页。使用带有ROLLUP运算符的GROUPBY子句使用带有

ROLLUP

运算符的

GROUPBY

子句使用GROUPBY子句和ROLLUP操作符时，应注意SQLServer将在结果集中增加一行，这行将显示总和或平均值之类的汇总值，新增的行以NULL标识不能同时使用关键字ALL和操作符ROLLUP第67页/共88页第六十七页，共89页。使用GROUPING函数使用GROUPING函数如何区分CUBE操作或ROLLUP运算符所生成的NULL值和从实际数据中返回的NULL值？使用GROUPING区分空值如果列中的值来自事实数据，则GROUPING函数返回1；如果列中的值是CUBE操作或ROLLUP操作所生成的NULL，则返回0。在CUBE或ROLLUP操作中，所生成的NULL代表全体值。第68页/共88页第六十八页，共89页。insertintocpvalues('table',null,202)insertintocpvalues('table',null,180)selectitem,grouping(item),color,grouping(color),sum(quantity)fromcpgroupbyitem,colorwithcube第69页/共88页第六十九页，共89页。描述使用带有ROLLUP运算符的GROUPBY子句SELECTproductid,orderid,SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductid,orderidWITHROLLUPORDERBYproductid,orderid

GOproductidorderidtotal_quantityNULLNULL951NULL1511512102NULL35211022253NULL4531153230示例总数只对productid1中的行汇总productid1、

orderid1的具体值productid1、

orderid2的具体值只对

productid2中的行汇总productid2、

orderid1的具体值productid2、orderid

2的具体值只对

productid3中的行汇总productid3、orderid

1的具体值productid3、orderid2的具体值第70页/共88页第七十页，共89页。使用带有CUBE运算符的GROUPBY子句CUBE操作符比ROLLUP操作符

多产生两个汇总值SELECTproductid,orderid,SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductid,orderidWITHCUBEORDERBYproductid,orderid

GO描述总数对orderid1中的所有行汇总对orderid2中的所有行汇总只对productid1中的行汇总productid1、orderid1的具体值productid1、orderid2的具体值只对productid2中的行汇总productid2、orderid1的具体值productid2、orderid2的具体值只对productid3中的行汇总productid3、orderid1的具体值productid3、orderid2的具体值productidorderidtotal_quantityNULLNULL95NULL130NULL2651NULL1511512102NULL35211022253NULL4531153230示例第71页/共88页第七十一页，共89页。1代表前一字段的汇总值0代表前一字段的具体值95306515510351025451530使用GROUPING函数SELECTproductid,GROUPING(productid),orderid,GROUPING(orderid),SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductid,orderidWITHCUBEORDERBYproductid,orderid

GOproductidNULLNULLNULL111222333111000000000orderidNULL12NULL12NULL12NULL12100100100100total_quantity示例第72页/共88页第七十二页，共89页。问题：ROLLUP和CUBE的区别在什么地方？CUBE生成的结果集显示了所选列中值的所有组合的聚合;ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。第73页/共88页第七十三页，共89页。使用TOPn列出前n

个记录使用TABLESAMPLE限制结果集使用聚合函数GROUPBY的基础知识在结果集中生成汇总值使用COMPUTE和COMPUTEBY子句推荐操作2、数据分组与汇总第74页/共88页第七十四页，共89页。使用COMPUTE和COMPUTEBY子句8.2.6使用COMPUTE和COMPUTEBY子句生成某一列的明细值和汇总值的报表为组中的子集生成明细值和汇总值的报表第75页/共88页第七十五页，共89页。使用COMPUTE和COMPUTEBY子句（续）COMPUTEBYCOMPUTESELECTproductid,orderid,quantityFROMorderhistORDERBYproductid,orderidCOMPUTESUM(quantity)BYproductidCOMPUTESUM(quantity)SELECTproductid,orderid,quantityFROMorderhistORDERBYproductid,orderidCOMPUTESUM(quantity)productidorderidquantity11512102110222531153230sum95productidorderidquantity1151210sum15211022