《代码C8-操作结果集.doc》由会员分享,可在线阅读,更多相关《代码C8-操作结果集.doc(17页珍藏版)》请在金锄头文库上搜索。
1、第8章 操作结果集8.1 合并结果集8.1.1 使用UNION与UNION ALL进行结果集合并使用下面的语句创建Table1和Table2两个表,并向其中插入部分示例数据,内容分别如表8-1和表8-2所示。CREATE TABLE Table1 (A int, B char(4), C char(4);CREATE TABLE Table2 (A char(4), B decimal(5,4); INSERT INTO Table1 VALUES (1, ABC, JKL), (2, DEF, MNO), (3, GHI, PQR); INSERT INTO Table2 VALUES (J
2、KL, 1.000), (DEF, 2.000), (MNO, 5.000);8.1.2 使用ORDER BY子句对合并结果集排序因此,在执行ORDER BY等操作时必须使用第一个结果集中的列名称。如:SELECT a, b FROM table1UNION SELECT b, a FROM table2ORDER BY a;例如,下面语句中的第一个WHERE应用于table1,第二个WHERE应用于table2,而ORDER BY则是应用于合并后的结果集。SELECT a, b FROM table1WHERE b = ABCUNION SELECT b, a FROM table2WHER
3、E a = DEFORDER BY a;8.1.3 结果集的合并顺序例如,下面的语句首先对table1和table2进行完全合并,然后将得到的结果集再与table3进行合并,并删除它们之间的重复行。SELECT * FROM table1UNION ALLSELECT * FROM table2UNIONSELECT * FROM table3例如,下面两个语句是等价的。第一条语句使用圆括号指定先将table2和table3合并,然后再将得到的结果集与table1合并;第二条语句则是改变了SELECT语句的书写顺序。SELECT * FROM table1UNION ALL(SELECT *
4、FROM table2 UNION SELECT * FROM table3);SELECT * FROM table2UNIONSELECT * FROM table3UNION ALL SELECT * FROM table1;8.2 查询结果集的差异行8.2.1 使用EXCEPT运算符首先使用下面的语句创建TableA、TableB和TableC三个结构相同的示例表,表的内容如表8-5所示。CREATE TABLE TableA (coll int);CREATE TABLE TableB (coll int);CREATE TABLE TableC (coll int);INSERT
5、INTO TableA VALUES (NULL),(NULL),(NULL),(1),(2),(2),(2),(3),(4),(4);INSERT INTO TableB VALUES (NULL),(1),(3),(4),(4),(5);INSERT INTO TableC VALUES (2),(2),(4),(4);下面的语句将返回位于TableA但是不位于TableB中的行,分析这两个表可以看出,TableA中只有coll为2的行在TableB中不存在,返回结果如表8-6所示。SELECT * FROM TableA EXCEPT SELECT * FROM TableB;表8-6返
6、回结果coll2下面语句的返回结果如表8-7所示。SELECT DISTINCT * FROM TableAWHERE NOT EXISTS (SELECT * FROM TableB WHERE coll = TableA.coll);8.2.2 查询全部差异行EXCEPT虽然可以返回左侧查询与右侧查询的差异行,但是它无法反映这种差异程度SELECT * FROM TableA EXCEPT SELECT * FROM TableB;SELECT * FROM TableC EXCEPT SELECT * FROM TableB;下面两行查询语句的结果分别如表8-9和表8-10所示。SELE
7、CT ROW_NUMBER() OVER(PARTITION BY coll ORDER BY coll) AS rn, collFROM TableA; SELECT ROW_NUMBER() OVER(PARTITION BY coll ORDER BY coll) AS rn, collFROM TableB;下面是完整查询语句:SELECT collFROM (SELECT ROW_NUMBER() OVER(PARTITION BY coll ORDER BY coll) AS rn, coll FROM TableA EXCEPT SELECT ROW_NUMBER() OVER(
8、PARTITION BY coll ORDER BY coll) AS rn, coll FROM TableB ) AS tmpTable;8.3 查询结果集的相同行8.3.1 使用INTERSECT运算符返回结果如表8-11所示。SELECT * FROM TableAINTERSECT SELECT * FROM TableB;因此下面查询语句的返回结果不包含表8-11中所示的NULL行。SELECT DISTINCT * FROM TableAWHERE EXISTS (SELECT * FROM TableB WHERE coll = TableA.coll);8.3.2 查询全部相
9、同行然后再对这两个表的行进行INTERSECT操作。SELECT collFROM (SELECT ROW_NUMBER() OVER(PARTITION BY coll ORDER BY coll) AS rn, coll FROM TableA INTERSECT SELECT ROW_NUMBER() OVER(PARTITION BY coll ORDER BY coll) AS rn, coll FROM TableB )AS tmpTable;8.4 UNION、EXCEPT和INTERSECT的执行顺序例如,下面的查询使用了三个运算符:SELECT * FROM TableBUN
10、IONSELECT * FROM TableA EXCEPT SELECT * FROM TableB INTERSECT SELECT * FROM TableC;例如,下面的语句首先将TableA与TableB执行EXCEPT,得到中间查询结果后,由于INTERSECT的优先级高于UNION,则先将中间查询结果与TableC执行INTERSECT,最后再与TableB执行UNION。从语句的执行计划,也可以看出这种执行顺序,如图8-1所示。SELECT * FROM TableBUNION(SELECT * FROM TableA EXCEPT SELECT * FROM TableB)I
11、NTERSECT SELECT * FROM TableC;8.5 在其他语句中使用UNION、EXCEPT和INTERSECT8.5.1 使用INTO子句指定结果存储位置例如,下面的语句将结果集操作的结果存储到临时表#T1中,并读取#T1中的数据。SELECT * INTO #T1 FROM TableA EXCEPT SELECT * FROM TableBINTERSECT SELECT * FROM TableC;SELECT * FROM #T1;8.5.2 突破结果集操作的限制例如,下面的语句通过派生表tmpTable解决了不能对最终结果集使用GROUP BY子句的限制。将Tabl
12、eA与TableB完全合并后,计算coll中每项的数目,返回结果如表8-16所示。SELECT coll, COUNT(*) AS cnFROM (SELECT * FROM TableA UNION ALL SELECT * FROM TableB) AS tmpTableGROUP BY coll;它并不是对TableA与TableB合并后的结果进行分组统计。SELECT coll, 1 AS cn FROM TableAUNION ALLSELECT coll, COUNT(*) AS cn FROM TableBGROUP BY coll;又如,下面的语句通过派生表和添加一个辅助列Or
13、derCol,实现了对TableA按升序排序、对TableB按降序排序,查询结果如表8-17所示。SELECT * FROM (SELECT 1 AS OrderCol, coll FROM TableA UNION SELECT 2 AS OrderCol, coll FROM TableB) AS tmpTableORDER BY OrderCol, CASE WHEN OrderCol = 1 THEN coll END, CASE WHEN OrderCol = 2 THEN coll END DESC; 8.6 使用公用表表达式8.6.1 CTE的语法结构1CTE的结构定义下面的示例
14、显示了CTE结构的组件:CTE的名称(SalesCTE)、列列表和查询。SalesCTE包含由OrderYear和SaleTotal两个列。返回的数据如表8-18所示。USE AdventureWorks;GOWITH SalesCTE (OrderYear, SaleTotal) - 定义CTE表达式的名称和列AS( SELECT YEAR(OrderDate), SUM(SubTotal) - 定义CTE查询语句 FROM Sales.SalesOrderHeader GROUP BY YEAR(OrderDate)SELECT * FROM SalesCTEORDER BY OrderYear; - 引用CTECTE开头部分对列名称的定义也可以在CTE查询语句中使用列别名的方式定义,如:WITH SalesCTE AS( SELECT YEAR(OrderDate) AS OrderYear, - 使用别名方式定义CTE的列名称 SUM(SubTotal) AS SaleTotal FROM Sales.Sale