A. 使用 SELECT 检索行和列下例显示三个代码示例。第一个代码示例从 pubs 数据库内的 authors 表中返回所有行(没有指定 WHERE 子句)和所有列(使用 *)。USEpubsSELECT*FROMauthorsORDERBY au_lname ASC, au_fname ASC --Alternate way.USEpubsSELECTauthors.*FROMcustomersORDERBY au_lname ASC, au_fname ASC 下例从 pubs 数据库内的 authors 表中返回所有行(没有指定 WHERE 子句)和列的一个子集(au_lname、au_fname、phone、city、state)。另外,还添加列标题。USEpubsSELECTau_fname, au_lname, phone AS Telephone, city, stateFROMauthorsORDERBY au_lname ASC, au_fname ASC 下例只返回居住在加利福尼亚州且不姓 McBadden 的作者列。USE pubsSELECTau_fname, au_lname, phone AS TelephoneFROMauthorsWHEREstate = 'CA' and au_lname <> 'McBadden'ORDERBY au_lname ASC, au_fname ASC B. 在列标题和列计算中使用 SELECT这些示例返回 titles 内的所有行。第一个示例返回本年度截止到现在的销售总额以及应付给每个作者和出版商的金额。在第二个示例中,计算每本书的总收入。USEpubsSELECTytd_sales AS Sales, authors.au_fname + ' '+ authors.au_lname ASAuthor, ToAuthor = (ytd_sales * royalty) / 100, ToPublisher = ytd_sales - (ytd_sales *royalty) / 100FROMtitles INNER JOIN titleauthor ON titles.title_id = titleauthor.title_idINNER JOIN authors ON titleauthor.au_id = authors.au_idORDERBY Sales DESC, Author ASC 下面是结果集:Sales Author ToAuthor ToPublisher------------------------------------ ----------- -----------22246 Anne Ringer 5339 1690722246 Michel DeFrance 5339 1690718722 Marjorie Green 4493 1422915096 Reginald Blotchet-Halls 2113 129838780 Cheryl Carson 1404 73764095 Abraham Bennet 409 36864095 Akiko Yokomoto 409 36864095 Ann Dull 409 36864095 Burt Gringlesby 409 36864095 Dean Straight 409 36864095 Marjorie Green 409 36864095 Michael O'Leary 409 36864095 Sheryl Hunter 409 36864072 Johnson White 407 36653876 Michael O'Leary 387 34893876 Stearns MacFeather 387 34893336 Charlene Locksley 333 30032045 Albert Ringer 245 18002045 Anne Ringer 245 18002032 Innes del Castillo 243 1789375 Livia Karsen 37 338375 Stearns MacFeather 37 338375 Sylvia Panteley 37 338111 Albert Ringer 11 100NULL Charlene Locksley NULL NULL (25row(s) affected) 下面是用于计算每本书的总收入的查询:USEpubsSELECT'Total income is', price * ytd_sales AS Revenue,'for',title_id AS Book#FROMtitlesORDERBY Book# ASC 下面是结果集:Revenue Book# ------------------------------------ ---- ------Totalincome is 81859.0500 for BU1032Totalincome is 46318.2000 for BU1111Totalincome is 55978.7800 for BU2075Totalincome is 81859.0500 for BU7832Totalincome is 40619.6800 for MC2222Totalincome is 66515.5400 for MC3021Totalincome is NULL for MC3026Totalincome is 201501.0000 for PC1035Totalincome is 81900.0000 for PC8888Totalincome is NULL for PC9999Totalincome is 8096.2500 for PS1372Totalincome is 22392.7500 for PS2091Totalincome is 777.0000 for PS2106Totalincome is 81399.2800 for PS3333Totalincome is 26654.6400 for PS7777Totalincome is 7856.2500 for TC3218Totalincome is 180397.2000 for TC4203Totalincome is 61384.0500 for TC7777 (18row(s) affected) C. 将 DISTINCT 与 SELECT 一起使用下例使用 DISTINCT 防止检索重复的作者 ID 号:USEpubsSELECTDISTINCT au_idFROMauthorsORDERBY au_id D. 使用 SELECT INTO 创建表第一个示例在tempdb 中创建一个名为 #coffeetabletitles 的临时表。为使用该表,始终用下面显示的精确名称(包括井号 (#))引用它。USEpubsDROPTABLE #coffeetabletitlesGOSETNOCOUNT ONSELECT* INTO #coffeetabletitlesFROMtitlesWHEREprice < $20SETNOCOUNT OFFSELECTnameFROMtempdb..sysobjectsWHEREname LIKE '#c%' 下面是结果集:name ------------------------------------------------------------------------#coffeetabletitles__________________________________________________________________________________________________000000000028 (1row(s) affected) CHECKPOINTingdatabase that was changed. (12row(s) affected) name ------------------------------------------------------------------------newtitles (1row(s) affected) CHECKPOINTingdatabase that was changed. 第二个示例创建一个名为 newtitles 的永久表。USEpubsIFEXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'newtitles') DROP TABLE newtitlesGOEXECsp_dboption 'pubs', 'select into/bulkcopy', 'true'USEpubsSELECT* INTO newtitlesFROM titlesWHEREprice > $25 OR price < $20SELECTname FROM sysobjects WHERE name LIKE 'new%'USEmasterEXECsp_dboption 'pubs', 'select into/bulkcopy', 'false' 下面是结果集:name ------------------------------newtitles (1row(s) affected) E. 使用相关子查询下例显示在语义上相当的查询并说明使用 EXISTS 关键字和 IN 关键字的区别。下面是两个示例,显示一个有效子查询检索书名为商业书籍的每个出版商名称,还检索 titles 表和 publishers 表之间相匹配的出版商 ID 号。USEpubsSELECTDISTINCT pub_nameFROMpublishersWHEREEXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'business') -- OrUSEpubsSELECTdistinct pub_nameFROMpublishersWHEREpub_id IN (SELECT pub_id FROM titles WHERE type = 'business') 下例在一个相关(或重复)子查询中使用 IN,该查询的值取决于外部查询。它被重复执行,为外部查询可能选择的每行各执行一次。该查询在 titleauthor 表中检索每个版税为 100% 且作者标识号在 titleauthor 表和 authors 中相匹配的作者的名和姓。USEpubsSELECTDISTINCT au_lname, au_fnameFROMauthorsWHERE 100 IN (SELECT royaltyper FROM titleauthor WHERE titleauthor.au_id = authors.au_id) 不能独立于外部查询对上述语句中的子查询取值。它需要一个 authors.au_id 值,但是该值随 Microsoft? SQL Server? 检查 authors 中的不同行而改变。相关子查询还可以用于外部查询的 HAVING 子句。下例查找那些预付款最大金额是组平均值两倍以上的书籍类型。USEpubsSELECTt1.typeFROMtitles t1GROUPBY t1.typeHAVINGMAX(t1.advance) >= ALL (SELECT 2 * AVG(t2.advance) FROM titles t2 WHEREt1.type = t2.type) 下例使用两个相关子查询查找作者姓名,这些作者至少参与过一本受欢迎的计算机书籍的创作。USEpubsSELECTau_lname, au_fnameFROMauthorsWHEREau_id IN (SELECT au_id FROM titleauthor WHERE title_id IN (SELECT title_id FROM titles WHERE type = 'popular_comp')) F. 使用 GROUP BY下例在数据库内查找各出版商的本年度截止到现在的销售总额。USEpubsSELECTpub_id, SUM(ytd_sales) AS totalFROMtitlesGROUPBY pub_idORDERBY pub_id 下面是结果集:pub_id total------ -----0736 282860877 442191389 24941 (3row(s) affected) 由于使用了 GROUP BY 子句,只为每个出版商各返回一个含有销售总额的行。G. 对多个组使用 GROUP BY下例查找按类型和出版商 ID 分组的平均价格和本年度截止到现在的销售总额。USEpubsSELECTtype, pub_id, AVG(price) AS 'avg', sum(ytd_sales) AS 'sum'FROMtitlesGROUPBY type, pub_idORDERBY type, pub_id 下面是结果集:type pub_id avg sum ------------------ --------------------- -----------business 0736 2.9900 18722business 1389 17.3100 12066mod_cook 0877 11.4900 24278popular_comp1389 21.4750 12875psychology 0736 11.4825 9564psychology 0877 21.5900 375trad_cook 0877 15.9633 19566UNDECIDED 0877 NULL NULL (8row(s) affected) Warning,null value eliminated from aggregate. H. 使用 GROUP BY 和 WHERE下例在只检索预付款多于 $5,000 的行后,将结果分成组。USEpubsSELECTtype, AVG(price)FROMtitlesWHEREadvance > $5000GROUPBY typeORDERBY type 下面是结果集:type --------------------------------------business 2.99 mod_cook 2.99 popular_comp21.48 psychology 14.30 trad_cook 17.97 (5row(s) affected) I. 将 GROUP BY 与表达式一起使用下例按表达式分组。如果表达式不包含聚合函数,则可以按表达式分组。USEpubsSELECTAVG(ytd_sales), ytd_sales * royaltyFROMtitlesGROUPBY ytd_sales * royaltyORDERBY ytd_sales * royalty 下面是结果集:----------------------NULL NULL 111 1110 375 3750 2032 24384 2045 24540 3336 33360 3876 38760 4072 40720 4095 40950 8780 140480 15096 211344 18722 449328 22246 533904 (13row(s) affected) J. 比较 GROUP BY 和 GROUP BY ALL第一个示例只为要求 10% 版税的书籍生成组。由于没有含 10% 版税的现代烹调书籍,因此结果中没有 mod_cook 类型的组。第二个示例为所有类型均生成组,包括现代烹调书籍和 UNDECIDED,尽管现代烹调书籍组中没有任何行符合 WHERE 子句中指定的条件。对于没有符合条件的行的组,容纳聚合值的列(平均价格)为 NULL。USEpubsSELECTtype, AVG(price)FROMtitlesWHEREroyalty = 10GROUPBY typeORDERBY type 下面是结果集:type --------------------------------------business 17.31 popular_comp20.00 psychology 14.14 trad_cook 17.97 (4row(s) affected) --Using GROUP BY ALLUSEpubsSELECTtype, AVG(price)FROMtitlesWHEREroyalty = 10GROUPBY all typeORDERBY type 下面是结果集:type --------------------------------------business 17.31 mod_cook NULL popular_comp20.00 psychology 14.14 trad_cook 17.97 UNDECIDED NULL (6row(s) affected) K. 将 GROUP BY 与 ORDER BY 一起使用下例查找各类书籍的平均价格并按平均价格排序结果。USEpubsSELECTtype, AVG(price)FROMtitlesGROUPBY typeORDERBY AVG(price) 下面是结果集:type --------------------------------------UNDECIDED NULL mod_cook 11.49 psychology 13.50 business 13.73 trad_cook 15.96 popular_comp21.48 (6row(s) affected) L. 使用 HAVING 子句第一个示例显示带聚合函数的 HAVING 子句。该子句按类型分组 titles 表中的行,并且消除只包含一本书的组。第二个示例显示不带聚合函数的 HAVING 子句。该子句按类型分组 titles 表中的行,并且消除不是以字母 p 开头的类型。USEpubsSELECTtypeFROMtitlesGROUPBY typeHAVINGCOUNT(*) > 1ORDERBY type 下面是结果集:type ------------business mod_cook popular_comppsychology trad_cook (5row(s) affected) 该查询在 HAVING 子句中使用 LIKE 子句。USEpubsSELECTtypeFROMtitlesGROUPBY typeHAVINGtype LIKE 'p%'ORDERBY type 下面是结果集:type------------popular_comppsychology (2row(s) affected) M. 使用 HAVING 和 GROUP BY下例显示在一个 SELECT 语句中使用 GROUP BY、HAVING、WHERE 和 ORDERBY 子句。该语句生成组和汇总值,但却是在消除那些价格低于 $5 的书名后才生成组和汇总值。它还按 pub_id 组织结果。USEpubsSELECTpub_id, SUM(advance), AVG(price)FROMtitlesWHEREprice >= $5GROUPBY pub_idHAVINGSUM(advance) > $15000 AND AVG(price) < $20 AND pub_id > '0800'ORDERBY pub_id 下面是结果集:pub_id -------------------------------- --------------------------0877 26,000.00 17.89 1389 30,000.00 18.98 (2row(s) affected) N. 将 HAVING 与 SUM 和 AVG 一起使用下例按出版商分组 titles 表,并只包括那些支付的预付款总额超过 $25,000 且平均书价高于 $15 的出版商的组。USEpubsSELECTpub_id, SUM(advance), AVG(price)FROMtitlesGROUPBY pub_idHAVINGSUM(advance) > $25000ANDAVG(price) > $15 若要查看本年度截止到现在的销售额超过 $40,000 的出版商,请使用下面的查询:USEpubsSELECTpub_id, total = SUM(ytd_sales)FROMtitlesGROUPBY pub_idHAVINGSUM(ytd_sales) > 40000 如果想确保对每个出版商的计算中至少包含六本书,则使用 HAVING COUNT(*) > 5 消除返回的总数小于六本书的出版商。该查询是这样的:USEpubsSELECTpub_id, SUM(ytd_sales) AS totalFROMtitlesGROUPBY pub_idHAVINGCOUNT(*) > 5 下面是结果集:pub_id total------ -----0877 442191389 24941 (2row(s) affected) 使用该语句,返回了两行。消除了 New Moon Books (0736)。O. 使用 COMPUTE BY 计算组合计下例使用两个代码示例显示 COMPUTE BY 的用法。第一个代码示例使用一个带一个聚合函数的 COMPUTE BY,第二个代码示例使用一个带两个聚合函数的 COMPUTE BY 函数。下例先按书籍类型,再按书籍价格计算每类烹调书籍(价格高于 $10)的价格总和。USEpubsSELECTtype, priceFROMtitlesWHEREprice > $10 AND type LIKE '%cook'ORDERBY type, priceCOMPUTESUM(price) BY type 下面是结果集:type price ---------------------------------mod_cook 19.9900 (1row(s) affected) sum ---------------------19.9900 (1row(s) affected) type price ---------------------------------trad_cook 11.9500trad_cook 14.9900trad_cook 20.9500 (3row(s) affected) sum ---------------------47.8900 (1row(s) affected) 下例检索所有烹饪书籍的书籍类型、出版商标识号和价格。COMPUTE BY 子句使用两个不同的聚合函数。USEpubsSELECTtype, pub_id, priceFROMtitlesWHEREtype LIKE '%cook'ORDERBY type, pub_idCOMPUTESUM(price), MAX(pub_id) BY type 下面是结果集:type pub_id price ------------------ ---------------------mod_cook 0877 19.9900mod_cook 0877 2.9900 (2row(s) affected) sum max -------------------------22.9800 0877 (1row(s) affected) type pub_id price ------------------ ---------------------trad_cook 0877 20.9500trad_cook 0877 11.9500trad_cook 0877 14.9900 (3row(s) affected) sum max -------------------------47.8900 0877 (1row(s) affected) P. 使用不带 BY 的 COMPUTE 计算总计值可以使用不带 BY 的 COMPUTE 关键字生成总计值、总计数,等等。该语句查找超过 $20 的所有类型书籍的价格和预付款总计。USEpubsSELECTtype, price, advanceFROMtitlesWHEREprice > $20COMPUTESUM(price), SUM(advance) 在同一查询内可以使用 COMPUTE BY 和不带 BY 的 COMPUTE。该查询按类型查找价格总和和预付款总和,然后计算所有类型书籍的价格总计和预付款总计。USEpubsSELECTtype, price, advanceFROMtitlesWHEREtype LIKE '%cook'ORDERBY type, priceCOMPUTESUM(price), SUM(advance) BY typeCOMPUTESUM(price), SUM(advance) 下面是结果集:type price advance --------------------------------- ---------------------mod_cook 2.9900 15000.0000mod_cook 19.9900 .0000 (2row(s) affected) sum sum ------------------------------------------22.9800 15000.0000 (1row(s) affected) type price advance --------------------------------- ---------------------trad_cook 11.9500 4000.0000trad_cook 14.9900 8000.0000trad_cook 20.9500 7000.0000 (3row(s) affected) sum sum ------------------------------------------47.8900 19000.0000 (1row(s) affected) sum sum ------------------------------------------70.8700 34000.0000 (1row(s) affected) Q. 计算所有行上的计算总和下例只显示选择列表内的三列,并在结果的最后提供基于所有价格和所有预付款的合计。USEpubsSELECTtype, price, advanceFROMtitlesCOMPUTESUM(price), SUM(advance) 下面是结果集:type price advance --------------------------------- ---------------------business 19.9900 5000.0000business 11.9500 5000.0000business 2.9900 10125.0000business 19.9900 5000.0000mod_cook 19.9900 .0000mod_cook 2.9900 15000.0000UNDECIDED NULL NULLpopular_comp22.9500 7000.0000popular_comp20.0000 8000.0000popular_compNULL NULLpsychology 21.5900 7000.0000psychology 10.9500 2275.0000psychology 7.0000 6000.0000psychology 19.9900 2000.0000psychology 7.9900 4000.0000trad_cook 20.9500 7000.0000trad_cook 11.9500 4000.0000trad_cook 14.9900 8000.0000 (18row(s) affected) sum sum ------------------------------------------236.2600 95400.0000 (1row(s) affected) Warning,null value eliminated from aggregate. R. 使用多个 COMPUTE 子句下例查找所有心理学书籍的价格总和,以及按出版商分类的心理学书籍的价格总和。通过包含一个以上的 COMPUTE BY 子句,可以在同一语句内使用不同的聚合函数。USEpubsSELECTtype, pub_id, priceFROMtitlesWHEREtype = 'psychology'ORDERBY type, pub_id, price COMPUTESUM(price) BY type, pub_idCOMPUTESUM(price) BY type 下面是结果集:type pub_id price ------------------ ---------------------psychology 0736 7.0000psychology 0736 7.9900psychology 0736 10.9500psychology 0736 19.9900 (4row(s) affected) sum ---------------------45.9300 (1row(s) affected) type pub_id price ------------------ ---------------------psychology 0877 21.5900 (1row(s) affected) sum ---------------------21.5900 (1row(s) affected) sum ---------------------67.5200 (1row(s) affected) S. 比较 GROUP BY 与 COMPUTE第一个示例使用 COMPUTE 子句计算不同类型烹调书籍的价格总和。第二个示例只使用 GROUP BY 生成相同的汇总信息。USEpubs--Using COMPUTESELECTtype, priceFROMtitlesWHEREtype like '%cook'ORDERBY type, price COMPUTESUM(price) BY type 下面是结果集:type price ---------------------------------mod_cook 2.9900mod_cook 19.9900 (2row(s) affected) sum ---------------------22.9800 (1row(s) affected) type price ---------------------------------trad_cook 11.9500trad_cook 14.9900trad_cook 20.9500 (3row(s) affected) sum ---------------------47.8900 (1row(s) affected) 下面是另一个使用 GROUP BY 的查询:USEpubs--Using GROUP BYSELECTtype, SUM(price)FROMtitlesWHEREtype LIKE '%cook'GROUPBY typeORDERBY type 下面是结果集:type ---------------------------------mod_cook 22.9800trad_cook 47.8900 (2row(s) affected) T. 将 SELECT 与 GROUP BY、COMPUTE和 ORDER BY 子句一起使用下例只返回含有本年度截止到现在的当前销售额的行,然后按 type 以递减顺序计算书籍的平均价格和预付款总额。将返回四个数据列,包括截断的书名。所有的计算列都出现在选择列表内。USEpubsSELECTCAST(title AS char(20)) AS title, type, price, advanceFROMtitlesWHEREytd_sales IS NOT NULLORDERBY type DESCCOMPUTEAVG(price), SUM(advance) BY typeCOMPUTESUM(price), SUM(advance) 下面是结果集:title type price advance -------------------------------- --------------------- ----------------Onions,Leeks, and G trad_cook 20.9500 7000.0000FiftyYears in Bucki trad_cook 11.9500 4000.0000Sushi,Anyone? trad_cook 14.9900 8000.0000 (3row(s) affected) avg sum ------------------------------------------15.9633 19000.0000 (1row(s) affected版商为所有作者售出的所有书名的总数量 所有出版商为每位作者售出的每个书名的数量 所有出版商为每位作者售出的所有书名的数量 每个出版商为所有作者售出的每个书名的数量 所有出版商为每位作者售出的每个书名的数量 说明 所有出版商、所有书名和所有作者的超聚合比销售总额大,因为许多书的作者不止一位。模式随关系数的增长而显现出来。报表中的值和 NULL 的模式显示哪些组形成了汇总聚合。有关组的显式信息由 GROUPING 函数提供。W. 将 GROUPING 函数与 CUBE 一起使用下例显示 SELECT 语句使用 SUM 聚合、GROUP BY 子句和 CUBE 运算符的方式。它还在 GROUP BY 子句后列出的两列上使用 GROUPING 函数。USEpubsSELECTpub_name, GROUPING(pub_name),title, GROUPING(title), SUM(qty) AS 'qty'FROMsales INNER JOIN titles ON sales.title_id = titles.title_id INNERJOIN publishers ON publishers.pub_id = titles.pub_idGROUPBY pub_name, titleWITHCUBE 结果集中有两个包含 0 和 1 值的列,这两列由 GROUPING(pub_name) 和 GROUPING(title) 表达式生成。下面是结果集:pub_name title qty ----------------------- ------------------------- --- -----------AlgodataInfosystems 0 But Is It UserFriendly? 0 30AlgodataInfosystems 0 Cooking with Computers:S 0 25AlgodataInfosystems 0 Secrets of Silicon Valley 0 50AlgodataInfosystems 0 Straight Talk AboutCompu 0 15AlgodataInfosystems 0 The Busy Executive'sData 0 15AlgodataInfosystems 0 NULL 1 135Binnet& Hardley 0 Computer Phobic ANDNon-P 0 20Binnet& Hardley 0 Fifty Years inBuckingham 0 20... ...NULL 1 The Busy Executive'sData 0 15NULL 1 The Gourmet Microwave 0 40NULL 1 You Can Combat ComputerS 0 35 (36row(s) affected) X. 使用 ROLLUP 运算符下例显示两个代码示例。第一个示例检索产品名称、客户名称和所下的订单总数并使用 ROLLUP 运算符。USEpubsSELECTproduct_name, customer_name, SUM(number_of_orders) AS 'Sum orders'FROMcube_examplesGROUPBY product_name, customer_nameWITHROLLUP 下面是结果集:product_name customer_name Sum orders------------------------------------------------------------ ----------FiloMix Eastern Connection 40 FiloMix Romero ytomillo 80 FiloMix WilmanKala 30 FiloMix NULL 150 Ikura Romero y tomillo 20 Ikura Wilman Kala 50 Ikura NULL 70 OutbackLager EasternConnection 10 OutbackLager Wilman Kala 30 OutbackLager NULL 40 NULL NULL 260 (11row(s) affected) 第二个示例显示在公司列和部门列上执行 ROLLUP 运算并合计出雇员总数。ROLLUP 运算符生成聚合汇总。该运算符用在需要汇总信息但完整的 CUBE 提供的都是无关的数据时,或者用在集内有集的情况中,例如公司内的部门就是集内的集。USEpubsCREATETABLE personnel( company_name varchar(20), department varchar(15), num_employees int) INSERTpersonnel VALUES ('Du monde entier', 'Finance', 10)INSERTpersonnel VALUES ('Du monde entier', 'Engineering', 40)INSERTpersonnel VALUES ('Du monde entier', 'Marketing', 40)INSERTpersonnel VALUES ('Piccolo und mehr', 'Accounting', 20)INSERTpersonnel VALUES ('Piccolo und mehr', 'Personnel', 30)INSERTpersonnel VALUES ('Piccolo und mehr', 'Payroll', 40) 在该查询中,除了 ROLLUP 计算结果外,公司名称、部门和公司内所有雇员的总数也成为结果集的一部分。SELECTcompany_name, department, SUM(num_employees)FROMpersonnelGROUPBY company_name, department WITH ROLLUP 下面是结果集:company_name department ----------------------------------- -----------Dumonde entier Engineering 40 Dumonde entier Finance 10 Dumonde entier Marketing 40 Dumonde entier NULL 90 Piccolound mehr Accounting 20 Piccolound mehr Payroll 40 Piccolound mehr Personnel 30 Piccolound mehr NULL 90 NULL NULL 180 (9row(s) affected) Y. 使用 GROUPING 函数下例将三个新行添加进 cube_examples 表中。三行中的每行都在一个或多个列中记录 NULL,以便只显示 ROLLUP 函数在分组列中生成值 1。另外,下例修改了在前面的示例中使用的 SELECT 语句。USEpubs-- Addfirst row with a NULL customer name and 0 orders.INSERTcube_examples (product_name, customer_name, number_of_orders) VALUES ('Ikura', NULL, 0) -- Addsecond row with a NULL product and NULL customer with real value-- fororders.INSERTcube_examples (product_name, customer_name, number_of_orders) VALUES (NULL, NULL, 50) -- Addthird row with a NULL product, NULL order amount, but a real--customer name.INSERTcube_examples (product_name, customer_name, number_of_orders)VALUES(NULL, 'Wilman Kala', NULL) SELECTproduct_name AS Prod, customer_name AS Cust, SUM(number_of_orders) AS 'Sum Orders', GROUPING(product_name) AS 'Grp prod_name', GROUPING(customer_name) AS 'Grp cust_name'FROMcube_examplesGROUPBY product_name, customer_nameWITHROLLUP GROUPING函数只能与 CUBE 或 ROLLUP 一起使用。表达式取值为 NULL 时,GROUPING 函数返回值 1,因为列值是 NULL 且代表所有值的设置。当相应的列(不管是否是 NULL)不是来自作为语法值的 CUBE 或 ROLLUP 选项时,GROUPING 函数返回值 0。返回值的数据类型为 tinyint。下面是结果集:Prod Cust Sum Orders Grp prod_name Grp cust_name------------------------------- ----------- ------------- -------------NULL NULL 50 0 0 NULL Wilman Kala NULL 0 0 NULL NULL 50 0 1 FiloMix Eastern Connection 40 0 0 FiloMix Romero y tomillo 80 0 0 FiloMix Wilman Kala 30 0 0 FiloMix NULL 150 0 1 Ikura NULL 0 0 0 Ikura Romero y tomillo 20 0 0 Ikura Wilman Kala 50 0 0 Ikura NULL 70 0 1 OutbackLager Eastern Connection 10 0 0 OutbackLager Wilman Kala 30 0 0 OutbackLager NULL 40 0 1 NULL NULL 310 1 1 (15row(s) affected) Z. 在 SELECT 中使用 GROUP BY、聚合函数和ROLLUP下例使用包含聚合函数和 GROUP BY 子句的 SELECT 查询,GROUP BY 子句按顺序先后列出 pub_name、au_lname和 title。USEpubsSELECTpub_name, au_lname, title, SUM(qty) AS 'SUM'FROMauthors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id INNERJOIN titles ON titles.title_id = titleauthor.title_idINNER JOIN publishers ON publishers.pub_id = titles.pub_id INNERJOIN sales ON sales.title_id = titles.title_idGROUPBY pub_name, au_lname, titleWITHROLLUP 通过使用 ROLLUP 运算符,沿列的列表从右到左移动以创建这些分组。pub_name au_lname title SUM(qty)pub_name au_lname NULL SUM(qty)pub_name NULL NULL SUM(qty)NULL NULL NULL SUM(qty) NULL 代表该列中的所有值。如果使用不带 ROLLUP 运算符的 SELECT 语句,该语句则创建单个分组。该查询返回每个 pub_name、au_lname和 title 唯一组合的总和值。pub_name au_lname title SUM(qty) 将这些示例与在同一查询上使用 CUBE 运算符所创建的分组进行比较。pub_name au_lname title SUM(qty)pub_name au_lname NULL SUM(qty)pub_name NULL NULL SUM(qty)NULL NULL NULL SUM(qty)NULL au_lname title SUM(qty)NULL au_lname NULL SUM(qty)pub_name NULL title SUM(qty)NULL NULL title SUM(qty) 分组对应于结果集中返回的信息。结果集中的 NULL 代表列中的所有值。当列(pub_name、au_lname和title)的顺序和GROUP BY 子句中列出的顺序一样时,ROLLUP 运算符返回下列数据:每个出版商为每位作者售出的每个书名的数量 每个出版商为每位作者售出的所有书名的数量 每个出版商售出的所有书名的数量 所有出版商为所有作者售出的所有书名的总数量下面是结果集:pub_name au_lname title SUM----------------------------- ------------------------------------ ---AlgodataInfosys Bennet The Busy Executive's Database Guide 15AlgodataInfosys Bennet NULL 15AlgodataInfosys Carson NULL 30AlgodataInfosys Dull Secrets of Silicon Valley 50AlgodataInfosys Dull NULL 50... ...NewMoon Books White Prolonged Data Deprivation: Four 15NewMoon Books White NULL 15NewMoon Books NULL NULL 316NULL NULL NULL 791 (49row(s) affected) GROUPING函数可以与 ROLLUP 运算符或 CUBE 运算符一起使用。该函数可以应用于选择列表中的一列。根据该列是否由 ROLLUP 运算符分组,该函数返回 1 或 0。a. 使用 INDEX 优化程序提示下例显示使用 INDEX 优化程序提示的两种方式。第一个示例显示强制优化程序使用非聚集索引检索表中的行,第二个示例显示强制使用 0 索引执行表扫描。-- Usethe specifically named INDEX.USEpubsSELECTau_lname, au_fname, phoneFROMauthors WITH (INDEX(aunmind))WHEREau_lname = 'Smith' 下面是结果集:au_lname au_fname phone ---------------------------------------------------------- ----------Smith Meander 913 843-0462 (1row(s) affected) --Force a table scan by using INDEX = 0.USEpubsSELECTemp_id, fname, lname, hire_dateFROMemployee (index = 0)WHEREhire_date > '10/1/1994' b. 使用 OPTION 和 GROUP 提示下例显示如何与 GROUP BY 子句一起使用 OPTION (GROUP) 子句。USEpubsSELECTa.au_fname, a.au_lname, SUBSTRING(t.title, 1, 15)FROMauthors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_idGROUPBY a.au_lname, a.au_fname, t.titleORDERBY au_lname ASC, au_fname ASCOPTION(HASH GROUP, FAST 10) c. 使用 UNION 查询提示下例显示使用 MERGE UNION 查询提示。USEpubsSELECT*FROMauthors a1OPTION(MERGE UNION)SELECT*FROMauthors a2 d. 使用简单 UNION下例中的结果集包括 Customers 和 SouthAmericanCustomers 这两个表的 ContactName、CompanyName、City 和 Phone列的内容。USENorthwindGOIFEXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME ='SouthAmericanCustomers') DROP TABLE SouthAmericanCustomersGO--Create SouthAmericanCustomers table.SELECTContactName, CompanyName, City, PhoneINTOSouthAmericanCustomersFROMCustomersWHERECountry IN ('USA', 'Canada')GO-- Hereis the simple union.USENorthwindSELECTContactName, CompanyName, City, PhoneFROMCustomersWHERECountry IN ('USA', 'Canada')UNIONSELECTContactName, CompanyName, City, PhoneFROMSouthAmericanCustomersORDERBY CompanyName, ContactName ASCGO e. 将 SELECT INTO 与 UNION 一起使用在下例中,第一个 SELECT 语句中的 INTO 子句指定名为 CustomerResults 的表包含由 Customers 和 SouthAmericanCustomers 表中指定列的并集组成的最终结果集。USENorthwindIFEXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CustomerResults') DROP TABLE CustomerResultsGOUSENorthwindSELECTContactName, CompanyName, City, Phone INTO CustomerResultsFROMCustomersWHERECountry IN ('USA', 'Canada')UNIONSELECTContactName, CompanyName, City, PhoneFROMSouthAmericanCustomersORDERBY CompanyName, ContactName ASCGO f. 在两个 SELECT 语句中将 UNION 与 ORDERBY 一起使用与 UNION 子句一起使用的某些参数的顺序非常重要。下例通过两个 SELECT 语句说明不正确和正确的 UNION 用法,并重命名这些语句输出的列。/*INCORRECT */USENorthwindGOSELECT CityFROMCustomersORDERBY CitiesUNIONSELECTCities = CityFROMSouthAmericanCustomersGO /*CORRECT */USENorthwindGOSELECTCities = CityFROMCustomers UNIONSELECT CityFROMSouthAmericanCustomersORDERBY CitiesGO g. 在三个 SELECT 语句中使用 UNION 以显示 ALL和圆括号的作用这些示例使用 UNION 组合三个表的结果,这三个表都有相同的 5 行数据。第一个示例使用 UNION ALL 显示重复的记录并返回全部 15 行。第二个示例使用不带 ALL 的 UNION,从组合的三个 SELECT 语句结果集中删除重复的行。最后一个示例在第一个 UNION 中使用 ALL,在第二个不带 ALL 的 UNION中用圆括号将 UNION 括在里面。第二个 UNION 因位于圆括号内而首先得到处理,并且因为没有使用 ALL 选项而返回 5 行且删除重复的行。这 5 行通过 UNION ALL 关键字与第一个 SELECT 的结果组合,且不删除这两个由 5 行组成的结果集之间重复的行。最终结果有 10 行。USENorthwindGOIFEXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CustomersOne') DROP TABLE CustomersOneGOIFEXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CustomersTwo') DROP TABLE CustomersTwoGOIFEXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CustomersThree') DROP TABLE CustomersThreeGOUSENorthwindGOSELECTContactName, CompanyName, City, Phone INTO CustomersOneFROMCustomersWHERECountry = 'Mexico'GOSELECTContactName, CompanyName, City, Phone INTO CustomersTwoFROMCustomersWHERECountry = 'Mexico'GOSELECTContactName, CompanyName, City, Phone INTO CustomersThreeFROMCustomersWHERECountry = 'Mexico'GO--Union ALLSELECTContactNameFROMCustomersOne UNION ALLSELECTContactNameFROMCustomersTwo UNION ALLSELECTContactNameFROMCustomersThreeGO USENorthwindGOSELECTContactNameFROMCustomersOne UNIONSELECTContactNameFROMCustomersTwo UNIONSELECTContactNameFROMCustomersThreeGO USENorthwindGOSELECTContactNameFROMCustomersOne UNION ALL ( SELECT ContactName FROM CustomersTwo UNION SELECT ContactName FROM CustomersThree )GO
Sql pubs
最新推荐文章于 2024-07-24 16:39:27 发布