用SELECT語句查詢記錄SQLServer程序設(shè)計語言

1.1使用SELECT語句檢索數(shù)據(jù)1.1.1使用SELECT語句1.1.2指定列1.1.3使用WHERE子句指定行第1頁/共88頁第一頁，共89頁。SELECT[ALL|DISTINCT][TOPn]<選擇列表>

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

[WHERE]<搜索條件>[GROUPBY]{<分組表達(dá)式>}[,…n][HAVING]<搜索條件>[ORDERBY]{<字段名[ASC|DESC]>}[,…n]基本語法使用SELECT語句用[]括起來的是可選項，SELECT是必需的選擇列表指定了要返回的列WHERE子句指定限制查詢的條件

在搜索條件中，可以使用比較操作符、字符串、邏輯操作符來限制返回的行數(shù)1.1.1使用SELECT語句第2頁/共88頁第二頁，共89頁。使用SELECT語句（續(xù)）FROM子句知道了返回的行和列所屬的表DISTINCT選項從結(jié)果集中消除了重復(fù)的行，TOPn

選項限定了要返回的行數(shù)GROUPBY子句是對結(jié)果集進(jìn)行分組HAVING子句是在分組的時候，對字段或表達(dá)式指定搜索條件ORDERBY子句對結(jié)果集按某種條件進(jìn)行排序

1.1.1使用SELECT語句第3頁/共88頁第三頁，共89頁。指定列1.1.2指定列在選擇列表中指定要從表中返回的列在選擇列表中可以包含字段名、表達(dá)式、所選關(guān)鍵字或要賦值的局部變量。其語法為<選擇列表>::=

{*|{表名|視圖名|表的別名}.*

|{字段名|表達(dá)式

|$IDENTITY|$ROWGUID}

[[AS]字段別名]

|字段別名=表達(dá)式

}[,...n]指定要檢索的字段時，請注意選擇列表將按照指定順序查找并顯示所查詢的字段不同的字段名用“，”分開在選擇列表中，避免或盡量少使用符號“*”。該符號代表選擇表中所有的字段第4頁/共88頁第四頁，共89頁。使用WHERE子句指定行1.1.3使用WHERE子句指定行通過使用WHERE子句，可以得到基于搜索條件的行<搜索條件>::=

{[NOT]<斷言>|(<搜索條件>)}

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

}[,...n]<斷言>列出了可以在WHERE子句中包含的表達(dá)式列表使用WHERE子句指定行時，請注意在所有數(shù)據(jù)類型為char、nchar、varchar、nvarchar、text、datetime和smalldatetime的數(shù)據(jù)周圍，必須使用單引號（‘）在使用SELECT語句時，盡量使用WHERE語句限制將要返回的行第5頁/共88頁第五頁，共89頁。使用WHERE子句指定行（續(xù)）employeeidlastnamefirstnametitle5BuchananStevenSalesManagerUSEnorthwindSELECTemployeeid,lastname,firstname,titleFROMemployeesWHEREemployeeid=5GO示例1.1.3使用WHERE子句指定行第6頁/共88頁第六頁，共89頁。1、檢索數(shù)據(jù)1.1使用SELECT語句檢索數(shù)據(jù)1.2過濾數(shù)據(jù)1.3設(shè)置結(jié)果集格式1.4需要考慮的性能問題1.5推薦操作第7頁/共88頁第七頁，共89頁。過濾數(shù)據(jù)1.2過濾數(shù)據(jù)過濾的類型搜索條件使用比較運(yùn)算符＝、>、<、>=、<=、<>使用字符串比較符LIKE和NOTLIKE使用邏輯運(yùn)算符AND、OR檢索一定范圍內(nèi)的值BETWEEN和NOTBETWEEN使用值列表作為搜索條件IN和NOTIN檢索未知值ISNULL和ISNOTNULL第8頁/共88頁第八頁，共89頁。使用比較運(yùn)算符1.2.1使用比較運(yùn)算符使用比較操作符來比較表中的值與指定的值或表達(dá)式的值USEnorthwindSELECTlastname,cityFROMemployeesWHEREcountry='USA'GO第9頁/共88頁第九頁，共89頁。使用字符串比較符1.2.2使用字符串比較符在比較字符串時，可以使用LIKE和通配符來查找需要的行通配符的種類通配符描述%0或多個字符串_任何單個的字符[]在指定區(qū)域或集合內(nèi)的任何單個字符[^]不在指定區(qū)域或集合內(nèi)的任何單個字符第10頁/共88頁第十頁，共89頁。使用字符串比較符（續(xù)）1.2.2使用字符串比較符表達(dá)式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頁。使用邏輯運(yùn)算符1.2.3使用邏輯運(yùn)算符用邏輯操作符AND、OR和NOT

來連接一系列的表達(dá)式，及優(yōu)化查詢過程。使用邏輯運(yùn)算符時，遵循下列原則使用AND返回滿足所有條件的行使用OR返回滿足任一條件的行使用NOT返回不滿足條件的行有多個表達(dá)式時，使用圓括號為表達(dá)式分組改變表達(dá)式求值順序增加表達(dá)式的可讀性第12頁/共88頁第十二頁，共89頁。使用邏輯運(yùn)算符（續(xù)）搜索條件的順序MicrosoftSQLServer首先求NOT表達(dá)式的值，然后是AND，最后是OR當(dāng)表達(dá)式中所有的操作符優(yōu)先級相同時，求值順序由左到右1.2.3使用邏輯運(yùn)算符(productnameLIKE'T%'ORproductid=46)

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

第13頁/共88頁第十三頁，共89頁。檢索一定范圍內(nèi)的值使用BETWEEN

來查詢在一定范圍內(nèi)的值，使用BETWEEN時，注意SQLServer返回的結(jié)果集中，包含范圍內(nèi)的邊緣值盡量使用BETWEEN，而不用AND和比較操作符表示的表達(dá)式（>=xAND<=y）。要使結(jié)果集不包含邊緣值，則使用（>xAND<y）要返回不在指定區(qū)域的行時，使用NOTBETWEEN1.2.4檢索一定范圍內(nèi)的值unitpriceBETWEEN10AND20第14頁/共88頁第十四頁，共89頁。使用值列表作為搜索條件1.2.5使用值列表作為搜索條件使用IN語句指定一個值的列表作為查詢條件。使用IN時，注意使用IN搜索條件和使用由OR操作符連起來的一系列比較操作符，其效果是等價的在搜索條件中不能包含NULL值，這將返回不可預(yù)測的結(jié)果集使用NOTIN搜索條件來返回值不在指定列表中的行。使用NOT會降低數(shù)據(jù)檢索速度countryIN('Japan','Italy')country='Japan‘orcountry='Italy'第15頁/共88頁第十五頁，共89頁。檢索未知值1.2.6檢索未知值輸入數(shù)據(jù)時，沒有指定一個字段的值，且此字段沒有定義默認(rèn)值的值，稱此字段含有空值（NULL）使用ISNULL搜索條件來查詢某指定列沒任何信息的行。需要返回含有空值的行時，注意把空值和任何表達(dá)式比較都會失敗，因為空值不等于任何表達(dá)式在CREATETABLE語句里，定義一個字段是否允許空值的存在使用ISNOTNULL來查詢指定列中非空的行ScoreISNULL第16頁/共88頁第十六頁，共89頁。1、檢索數(shù)據(jù)1.1使用SELECT語句檢索數(shù)據(jù)1.2過濾數(shù)據(jù)1.3設(shè)置結(jié)果集格式1.4需要考慮的性能問題1.5推薦操作第17頁/共88頁第十七頁，共89頁。設(shè)置結(jié)果集格式對數(shù)據(jù)進(jìn)行排序消除重復(fù)行改變列名使用字面值FOR子句UNION[ALL]1.3設(shè)置結(jié)果集格式第18頁/共88頁第十八頁，共89頁。對數(shù)據(jù)進(jìn)行排序1.3.1對數(shù)據(jù)進(jìn)行排序使用ORDERBY子句為結(jié)果集中的行排序時，有升序和降序兩種。使用ORDERBY時，注意除非使用ORDERBY指定順序，SQLServer并不主動為結(jié)果集中的行排序SQLServer缺省地按升序排列ORDERBY子句中指定的列并不一定要出現(xiàn)在選擇列表中可以按照字段名、計算出的值或表達(dá)式進(jìn)行排序可以在ORDERBY子句中引用某字段在選擇列表中的位置而進(jìn)行排序ORDERBY子句不能用于數(shù)據(jù)類型text、ntext或image字段第19頁/共88頁第十九頁，共89頁。對數(shù)據(jù)進(jìn)行排序（續(xù)）USEnorthwindSELECTproductid,productname,categoryid,unitprice

FROMproducts

ORDERBYcategoryid,unitpriceDESCGOproductidproductnamecategoryidunitprice38CotedeBlaye1263.500043IpohCoffee146.00002Chang119.0000…………63Vegie-spread243.90008NorthwoodsCranberrySauce240.000061Siropd'érable228.5000…………示例1.3.1對數(shù)據(jù)進(jìn)行排序第20頁/共88頁第二十頁，共89頁。消除重復(fù)行1.3.2消除重復(fù)行用DISTINCT可以消除重復(fù)行，使結(jié)果集列出惟一的值。使用DISTINCT時，注意除非指定DISTINCT子句，否則SELECT語句將返回所有滿足搜索條件的行選擇列表中所有列的組合值將決定其惟一性包含任何惟一組合值的行都將被檢索到并被返回到結(jié)果集中除非使用了ORDERBY子句，否則DISTINCT子句將按隨機(jī)的順序顯示結(jié)果集中的行第21頁/共88頁第二十一頁，共89頁。消除重復(fù)行（續(xù)）USEnorthwindSELECTDISTINCTcountry

FROMsuppliers

ORDERBYcountryGOcountryAustraliaBrazilCanadaDenmarkFinlandFranceGermanyItalyJapanNetherlandsNorwaySingaporeSpainSwedenUKUSA示例1.3.2消除重復(fù)行第22頁/共88頁第二十二頁，共89頁。改變列名1.3.3改變列名為了提高可讀性，可以為選擇列表中的字段名起別名改變字段名時，應(yīng)注意缺省情況下，結(jié)果集所顯示的字段名，就是在CREATETABLE語句中指定的字段名如果字段名中有空格，或字段名不符合SQLServer命名規(guī)則，用單引號來標(biāo)識這個字段名可以為經(jīng)過計算的包含函數(shù)、字符符號的字段起別名字段名中至多可以包含128個字符第23頁/共88頁第二十三頁，共89頁。改變列名（續(xù)）USEnorthwindSELECTfirstnameASFirst,lastnameLast ,'EmployeeID:'=employeeidFROMemployeesFirstLastEmployeeID:NancyDavolio1AndrewFuller2JanetLeverling3MargaretPeacock4StevenBuchanan5MichaelSuyama6RobertKing7LauraCallahan8AnneDodsworth91.3.3改變列名示例第24頁/共88頁第二十四頁，共89頁。使用字面值1.3.4使用字面值使用字面值可以增加結(jié)果集的可讀性，符號可能是字母、數(shù)字或標(biāo)識，它們被用作特定的值。其語法為SELECT字段名

|’字符串符號’

[，字段名

|’字符串符號’…]

FROM表名USEnorthwindSELECT

firstname,lastname ,'Identificationnumber:',employeeidFROMemployees

GO第25頁/共88頁第二十五頁，共89頁。FOR子句FOR子句用來指定BROWSE或XML選項，主要在應(yīng)用程序的開發(fā)中經(jīng)常使用。FOR子句生成XML的詳細(xì)使用介紹請參考第24章。本節(jié)通過一個使用FOR子句的示例，簡單說明其一般的功能。1.3.5FOR子句USEnorthwindSELECTcountryFROMsuppliersORDERBYcountryforxmlautoGO示例第26頁/共88頁第二十六頁，共89頁。UNION[ALL]將兩個或更多查詢的結(jié)果合并為單個結(jié)果集，該結(jié)果集包含聯(lián)合查詢中的所有查詢的全部行。UNION運(yùn)算不同于使用聯(lián)接合并兩個表中的列的運(yùn)算。1.3.6UNION[ALL]USEAdventureWorksSELECTProductModelID,NameFROMProduction.ProductModelUNIONSELECTProductModelID,NameFROMdbo.ProductModel2ORDERBYName;使用UNION合并兩個查詢結(jié)果集的基本規(guī)則：所有查詢中的列數(shù)和列的順序必須相同。數(shù)據(jù)類型必須兼容。第27頁/共88頁第二十七頁，共89頁。需要考慮的性能問題非邏輯操作會降低查詢速度

比如：NOTBETWEEN、NOTIN和ISNOTNULL

LIKE搜索條件會降低查詢速度使用精確查詢或在一定范圍的查詢可能會提高數(shù)據(jù)查詢速度ORDERBY子句可能降低數(shù)據(jù)查詢速度

因為SQLServer在返回第一行前，必須對結(jié)果集中所有的行進(jìn)行計算并排序1.4需要考慮的性能問題第28頁/共88頁第二十八頁，共89頁。推薦操作使用DISTINCT子句消除結(jié)果集中重復(fù)的行通過改變字段名和使用字面值來提高結(jié)果集的可讀性第29頁/共88頁第二十九頁，共89頁。第30頁/共88頁第三十頁，共89頁。提出問題我們遇到了下面的問題：我們擁有桌子和椅子兩種產(chǎn)品，每種產(chǎn)品都有紅色和藍(lán)色兩種顏色。每種顏色的桌子有多少庫存，每種顏色的椅子有多少庫存。椅子有多少庫存，不論它是什么顏色；桌子有多少庫存，不論它是什么顏色。紅色的商品一共有多少庫存，藍(lán)色的商品一共有多少庫存。這個時候大家發(fā)現(xiàn)，我們需要的是以不同商品種類和顏色進(jìn)行排列組合的小計報表。第31頁/共88頁第三十一頁，共89頁。8.2、數(shù)據(jù)分組與匯總使用TOPn列出前n個記錄使用TABLESAMPLE限制結(jié)果集使用聚合函數(shù)GROUPBY的基礎(chǔ)知識在結(jié)果集中生成匯總值使用COMPUTE和COMPUTEBY子句推薦操作第32頁/共88頁第三十二頁，共89頁。使用TOPn

列出前

n

個記錄8.2.1使用TOPn列出前n個記錄關(guān)鍵字TOPn

只列出結(jié)果集中前n

個或前n%的記錄。使用TOPn

或TOPnPERCENT時，應(yīng)注意在ORDERBY子句中指定值的范圍關(guān)鍵字TOP后使用無符號的整數(shù)如果TOPnPERCENT生成小數(shù)，則SQLServer將把這個數(shù)取整可以在結(jié)果集中用WITHTIES子句包含那些值相等的記錄，這時結(jié)果集中可以包含任意數(shù)目的行部分語法：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名的學(xué)生學(xué)號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個記錄限制結(jié)果集大小的另一種方法是在執(zhí)行一個語句之前執(zhí)行SETROWCOUNTn語句。SETROWCOUNT與TOP的不同之處在于：TOP子句適用于指定了該子句的單個SELECT語句。在執(zhí)行另一個SETROWCOUNT語句之前，SETROWCOUNT會一直有效，例如執(zhí)行SETROWCOUNT0將會關(guān)閉此選項。SETROWCOUNTn第35頁/共88頁第三十五頁，共89頁。使用TOPn列出前

n

個記錄使用TABLESAMPLE限制結(jié)果集使用聚合函數(shù)GROUPBY的基礎(chǔ)知識在結(jié)果集中生成匯總值使用COMPUTE和COMPUTEBY子句推薦操作8.2、數(shù)據(jù)分組與匯總第36頁/共88頁第三十六頁，共89頁。使用TABLESAMPLE限制結(jié)果集8.2.2使用TABLESAMPLE限制結(jié)果集

TABLESAMPLE子句將從FROM子句中的表返回的行數(shù)限制到樣本數(shù)或行數(shù)的某一百分比。不能對視圖使用TABLESAMPLE。部分語法：TABLESAMPLE[SYSTEM](sample_number[PERCENT|ROWS])第37頁/共88頁第三十七頁，共89頁。--執(zhí)行幾次代碼，所得到的結(jié)果是不同的USEAdventureWorks;GOSELECTcount(*)FROMSales.CustomerTABLESAMPLESYSTEM(10PERCENT);使用TABLESAMPLE限制結(jié)果集第38頁/共88頁第三十八頁，共89頁。使用TOPn列出前

n

個記錄使用TABLESAMPLE限制結(jié)果集使用聚合函數(shù)GROUPBY的基礎(chǔ)知識在結(jié)果集中生成匯總值使用COMPUTE和COMPUTEBY子句推薦操作8.2、數(shù)據(jù)分組與匯總第39頁/共88頁第三十九頁，共89頁。使用聚合函數(shù)（續(xù)）8.2.3使用聚合函數(shù)計算諸如平均值和總和的函數(shù)稱為聚合函數(shù)SQLServer對整個表或表里某個組中的字段進(jìn)行匯總、計算，然后生成單個的值可以在SELECT語句中單獨使用聚合函數(shù)，也可以與語句GROUPBY聯(lián)合使用除了COUNT函數(shù)，如果記錄集中沒有滿足WHERE子句的記錄，則所有函數(shù)返回空值，COUNT返回0字段的數(shù)據(jù)類型決定了可以用在該字段上的聚合函數(shù)類型如：SUM和AVG只能用在數(shù)據(jù)類型代表數(shù)字的字段上第40頁/共88頁第四十頁，共89頁。SUM和AVG。SUM和AVG分別用于求表達(dá)式中所有值項的總和與平均值。語法格式：

SUM|AVG(ALL|DISTINCT]expression)SUM|AVG忽略空值。使用聚合函數(shù)（續(xù)）8.2.3使用聚合函數(shù)第41頁/共88頁第四十一頁，共89頁。使用聚合函數(shù)（續(xù)）MAX和MIN。MAX和MIN分別用于求表達(dá)式中所有值項的最大值和最小值。語法格式：

MAX|MIN([ALL|DISTINCT]expression)

只能用在數(shù)字、字符、和時間日期數(shù)據(jù)類型。MAX|MIN忽略空值。8.2.3使用聚合函數(shù)第42頁/共88頁第四十二頁，共89頁。COUNT與COUNT(*)。用于統(tǒng)計組中滿足條件的行數(shù)或總行數(shù)。語法格式：

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

選擇*時將統(tǒng)計總行數(shù)。COUNT忽略空值，

COUNT(*)計算空值。使用聚合函數(shù)（續(xù)）8.2.3使用聚合函數(shù)第43頁/共88頁第四十三頁，共89頁。使用含有空值的聚合函數(shù)空值能使聚合函數(shù)產(chǎn)生意想不到的結(jié)果對包含空值的字段使用聚合函數(shù)時，應(yīng)注意SQLServer的聚合函數(shù)（COUNT(*)除外）將忽略字段中的空值COUNT（*）將計算所有的行，即使每個字段都含有空值使用含有空值的聚合函數(shù)第44頁/共88頁第四十四頁，共89頁。createtabletable1(snochar(5),agetinyint)insertintotable1values('95001',null)insertintotable1values('95002',null)insertintotable1values(null,null)insertintotable1values('95003',null)--以下返回的結(jié)果分別是什么selectcount(*)fromtable1selectcount(sno)fromtable1selectcount(age)fromtable1selectavg(age)fromtable1第45頁/共88頁第四十五頁，共89頁。ISNULL

如果第一個表達(dá)式為非NULL，返回第一個表達(dá)式，否則返回第二個表達(dá)式ISNULL()的使用ISNULL(表達(dá)式1，表達(dá)式2)第46頁/共88頁第四十六頁，共89頁。ISNULL()的使用

selectavg(salary)fromavg_testselectavg(isnull(salary,0))fromavg_test第47頁/共88頁第四十七頁，共89頁。常見的聚合函數(shù)及其描述聚合函數(shù)描述AVG 返回組中值的平均值?？罩祵⒈缓雎浴OUNT 表達(dá)式中值的數(shù)目COUNT(*)所選擇的行的數(shù)目MAX返回表達(dá)式的最大值。MAX忽略任何空值。對于字符列，MAX查找排序序列的最大值。MIN返回表達(dá)式的最小值。MIN忽略任何空值。對于字符列，MIN查找排序序列的最低值。SUM返回表達(dá)式中所有值的和，或只返回DISTINCT值。SUM只能用于數(shù)字列?？罩祵⒈缓雎?。GROUPING是一個聚合函數(shù),它產(chǎn)生一個附加的列，當(dāng)用CUBE或ROLLUP運(yùn)算符添加行時，附加的列輸出值為1，當(dāng)所添加的行不是由CUBE或ROLLUP產(chǎn)生時，附加列值為0。僅在與包含CUBE或ROLLUP運(yùn)算符的GROUPBY子句相聯(lián)系的選擇列表中才允許分組。分組用于區(qū)分由CUBE和ROLLUP返回的空值和標(biāo)準(zhǔn)的空值。作為CUBE或ROLLUP操作結(jié)果返回的NULL是NULL的特殊應(yīng)用。它在結(jié)果集內(nèi)作為列的占位符，意思是"全體"。第48頁/共88頁第四十八頁，共89頁。使用TOPn列出前

n個記錄使用TABLESAMPLE限制結(jié)果集使用聚合函數(shù)GROUPBY的基礎(chǔ)知識在結(jié)果集中生成匯總值使用COMPUTE和COMPUTEBY子句推薦操作8.2、數(shù)據(jù)分組與匯總第49頁/共88頁第四十九頁，共89頁。GROUPBY的基礎(chǔ)知識使用GROUPBY子句聯(lián)合使用GROUPBY子句和HAVING子句8.2.4GROUPBY的基礎(chǔ)知識第50頁/共88頁第五十頁，共89頁。聯(lián)合使用聚合函數(shù)和GROUPBY子句，能夠把表中的數(shù)據(jù)按字段分組，并對組中數(shù)據(jù)進(jìn)行匯總。語法：

GROUPBY[ALL]group_by_expression[,…n][WITH]{CUBE|ROLLUP}]]group_by_expression 用于分組的表達(dá)式，WITH指定CUBE或ROLLUP操作符。GROUPBY的基礎(chǔ)知識8.2.4GROUPBY的基礎(chǔ)知識第51頁/共88頁第五十一頁，共89頁。USEnorthwindSELECTproductid,orderid

,quantityFROMorderhist

GOproductidtotal_quantity115235345productidorderidquantity11512102110222531153230productidtotal_quantity235只對滿足WHERE子句的行分組USEnorthwindSELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistWHEREproductid=2GROUPBYproductid

GO示例2USEnorthwindSELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductid

GO示例1GROUPBY的基礎(chǔ)知識8.2.4GROUPBY的基礎(chǔ)知識第52頁/共88頁第五十二頁，共89頁。使用GROUPBY子句時，應(yīng)注意SQLServer將為每一組計算一個匯總值，并把匯總值保存在一個字段中對于指定的一組，SQLServer只生成一條記錄，不返回詳細(xì)信息SQLServer只對滿足WHERE子句的記錄進(jìn)行分組和匯總不要對可包含空值的字段使用GROUPBY子句，因為空值也將被當(dāng)作一組聯(lián)合使用關(guān)鍵字ALL和GROUPBY子句時，組合字段中含有空值的行都將被列出，而不管記錄是否滿足WHERE子句的條件使用GROUPBY子句使用GROUPBY子句第53頁/共88頁第五十三頁，共89頁。--下面的例子給出了針對所有type和pub_id的唯一組合進(jìn)行分類并統(tǒng)計計算的情況。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子句，因為空值也將被當(dāng)作一組第55頁/共88頁第五十五頁，共89頁。使用GROUPBY子句（續(xù)）SELECTproductid,orderid

,quantityFROMorderhist

GOSELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductid

GOproductidtotal_quantity115235345productidorderidquantity11511102110222531153230productidtotal_quantity235只對滿足WHERE子句的行分組SELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistWHEREproductid=2GROUPBYproductid

GO示例1示例使用GROUPBY子句第56頁/共88頁第五十六頁，共89頁。聯(lián)合使用GROUPBY子句和HAVING子句聯(lián)合使用GROUPBY子句和HAVING子句在分組的同時，對字段或表達(dá)式指定搜索條件使用HAVING子句時，應(yīng)注意只在使用GROUPBY子句的同時，使用HAVING子句來限制分組可以引用任何出現(xiàn)在選擇列表中的字段不要聯(lián)合使用關(guān)鍵字ALL和HAVING子句。因為HAVING子句會忽略ALL關(guān)鍵字，并返回只符合HAVING條件的組第57頁/共88頁第五十七頁，共89頁。聯(lián)合使用GROUPBY子句和HAVING子句（續(xù)）SELECTproductid,orderid,quantityFROMorderhist

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

GOproductidtotal_quantity235345productidorderidquantity11512102110222531153230聯(lián)合使用GROUPBY子句和HAVING子句第58頁/共88頁第五十八頁，共89頁。WHERE子句與HAVING子句的區(qū)別：

WHERE子句作用于基本表或視圖，從中選擇滿足條件的記錄；

HAVING子句作用于組，從中選擇滿足條件的組聯(lián)合使用GROUPBY子句和HAVING子句（續(xù)）聯(lián)合使用GROUPBY子句和HAVING子句第59頁/共88頁第五十九頁，共89頁。使用TOPn列出前n

個記錄使用TABLESAMPLE限制結(jié)果集使用聚合函數(shù)GROUPBY的基礎(chǔ)知識在結(jié)果集中生成匯總值使用COMPUTE和COMPUTEBY子句推薦操作8.2、數(shù)據(jù)分組與匯總第60頁/共88頁第六十頁，共89頁。在結(jié)果集中生成匯總值使用帶有CUBE運(yùn)算符的GROUPBY子句使用帶有ROLLUP運(yùn)算符的GROUPBY子句使用GROUPING函數(shù)8.2.5在結(jié)果集中生成匯總值第61頁/共88頁第六十一頁，共89頁。使用帶有CUBE運(yùn)算符的GROUPBY子句使用帶有CUBE運(yùn)算符的GROUPBY子句使用帶有CUBE運(yùn)算符的GROUPBY子句創(chuàng)建并匯總所有可能的基于GROUPBY子句分組的組合。使用帶有CUBE運(yùn)算符的GROUPBY子句用于提供標(biāo)準(zhǔn)的關(guān)系型格式的數(shù)據(jù)。第62頁/共88頁第六十二頁，共89頁。selectitem,color,sum(quantity)fromcpgroupbyitem,colorwithcubechair、blue的詳細(xì)值chair、red的詳細(xì)值匯總chair的所有記錄table、blue的詳細(xì)值table、red的詳細(xì)值匯總table的所有記錄總計匯總bule的所有記錄匯總red的所有記錄每種產(chǎn)品的每種顏色的總數(shù)量，每種產(chǎn)品的所有顏色的總數(shù)量，每種顏色的所有產(chǎn)品的總數(shù)量，所有產(chǎn)品所有顏色的總數(shù)量。第63頁/共88頁第六十三頁，共89頁。使用帶有CUBE運(yùn)算符的GROUPBY子句使用帶有CUBE運(yùn)算符的GROUPBY子句使用GROUPBY子句和CUBE操作符時，應(yīng)注意如果在GROUPBY子句中有

n個字段或表達(dá)式，SQLServer將在結(jié)果集中返回2n種可能的組合結(jié)果集中含有NULL的記錄代表該記錄由CUBE操作符生成（注意此NULL不是基本表中的實際空值）不能同時使用關(guān)鍵字ALL和操作符CUBE第64頁/共88頁第六十四頁，共89頁。使用帶有ROLLUP運(yùn)算符的GROUPBY子句使用帶有

ROLLUP

運(yùn)算符的

GROUPBY

子句ROLLUP運(yùn)算符的功能類似于CUBE運(yùn)算符。使用帶有ROLLUP運(yùn)算符的GROUPBY子句匯總分組值使用帶有ROLLUP運(yùn)算符的GROUPBY子句用于提供標(biāo)準(zhǔn)的關(guān)系型格式的數(shù)據(jù)。第65頁/共88頁第六十五頁，共89頁。selectitem,color,sum(quantity)fromcpgroupbyitem,colorwithrollupchair、blue的詳細(xì)值chair、red的詳細(xì)值匯總chair的所有記錄table、blue的詳細(xì)值table、red的詳細(xì)值匯總table的所有記錄總計每種產(chǎn)品的每種顏色的總數(shù)量，每種產(chǎn)品的所有顏色的總數(shù)量，所有產(chǎn)品所有顏色的總數(shù)量。第66頁/共88頁第六十六頁，共89頁。使用帶有ROLLUP運(yùn)算符的GROUPBY子句使用帶有

ROLLUP

運(yùn)算符的

GROUPBY

子句使用GROUPBY子句和ROLLUP操作符時，應(yīng)注意SQLServer將在結(jié)果集中增加一行，這行將顯示總和或平均值之類的匯總值，新增的行以NULL標(biāo)識不能同時使用關(guān)鍵字ALL和操作符ROLLUP第67頁/共88頁第六十七頁，共89頁。使用GROUPING函數(shù)使用GROUPING函數(shù)如何區(qū)分CUBE操作或ROLLUP運(yùn)算符所生成的NULL值和從實際數(shù)據(jù)中返回的NULL值？使用GROUPING區(qū)分空值如果列中的值來自事實數(shù)據(jù)，則GROUPING函數(shù)返回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運(yùn)算符的GROUPBY子句SELECTproductid,orderid,SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductid,orderidWITHROLLUPORDERBYproductid,orderid

GOproductidorderidtotal_quantityNULLNULL951NULL1511512102NULL35211022253NULL4531153230示例總數(shù)只對productid1中的行匯總productid1、

orderid1的具體值productid1、

orderid2的具體值只對

productid2中的行匯總productid2、

orderid1的具體值productid2、orderid

2的具體值只對

productid3中的行匯總productid3、orderid

1的具體值productid3、orderid2的具體值第70頁/共88頁第七十頁，共89頁。使用帶有CUBE運(yùn)算符的GROUPBY子句CUBE操作符比ROLLUP操作符

多產(chǎn)生兩個匯總值SELECTproductid,orderid,SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductid,orderidWITHCUBEORDERBYproductid,orderid

GO描述總數(shù)對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函數(shù)SELECTproductid,GROUPING(productid),orderid,GROUPING(orderid),SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductid,orderidWITHCUBEORDERBYproductid,orderid

GOproductidNULLNULLNULL111222333111000000000orderidNULL12NULL12NULL12NULL12100100100100total_quantity示例第72頁/共88頁第七十二頁，共89頁。問題：ROLLUP和CUBE的區(qū)別在什么地方？CUBE生成的結(jié)果集顯示了所選列中值的所有組合的聚合;ROLLUP生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚合。第73頁/共88頁第七十三頁，共89頁。使用TOPn列出前n

個記錄使用TABLESAMPLE限制結(jié)果集使用聚合函數(shù)GROUPBY的基礎(chǔ)知識在結(jié)果集中生成匯總值使用COMPUTE和COMPUTEBY子句推薦操作2、數(shù)據(jù)分組與匯總第74頁/共88頁第七十四頁，共89頁。使用COMPUTE和COMPUTEBY子句8.2.6使用COMPUTE和COMPUTEBY子句生成某一列的明細(xì)值和匯總值的報表為組中的子集生成明細(xì)值和匯總值的報表第75頁/共88頁第七十五頁，共89頁。使用COMPUTE和COMPUTEBY子句（續(xù)）COMPUTEBYCOMPUTESELECTproductid,orderid,quantityFROMorderhistORDERBYproductid,orderidCOMPUTESUM(quantity)BYproductidCOMPUTESUM(quantity)SELECTproductid,orderid,quantityFROMorderhistORDERBYproductid,orderidCOMPUTESUM(quantity)productidorderidquantity11512102110222531153230sum95productidorderidquantity1151210sum15211022