Sql pubs

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