《代码C8-操作结果集》由会员分享，可在线阅读，更多相关《代码C8-操作结果集（15页珍藏版）》请在金锄头文库上搜索。
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); INSE
2、RT INTO Table2 VALUES (JKL, 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 SEL
3、ECT b, a FROM table2WHERE a = DEFORDER BY a;8.1.3 结果集的合并顺序例如，下面的语句首先对table1和table2进行完全合并，然后将得到的结果集再与table3进行合并，并删除它们之间的重复行。SELECT * FROM table1UNION ALLSELECT * FROM table2UNIONSELECT * FROM table3例如，下面两个语句是等价的。第一条语句使用圆括号指定先将table2和table3合并，然后再将得到的结果集与table1合并；第二条语句则是改变了SELECT语句的书写顺序。SELECT * FROM t
4、able1UNION ALL(SELECT * 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 T
5、ableC (coll int);INSERT 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 SE
6、LECT * FROM TableB;表8-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;下面两行查询语
7、句的结果分别如表8-9和表8-10所示。SELECT 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 S
8、ELECT ROW_NUMBER() OVER(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 =
9、TableA.coll);8.3.2 查询全部相同行然后再对这两个表的行进行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的执行顺序例如，下面的查询使用了三个运算
10、符：SELECT * FROM TableBUNIONSELECT * 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 EXCEP
11、T SELECT * FROM TableB)INTERSECT 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解决了不能对最终
12、结果集使用GROUP BY子句的限制。将TableA与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
13、;又如，下面的语句通过派生表和添加一个辅助列OrderCol，实现了对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.
14、1 CTE的语法结构1CTE的结构定义下面的示例显示了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 Sale
