第六章 集合运算

use TSQLFundamentals2008;
go;

--6.1 UNION(并集)集合运算
--UNION ALL不会删除重复行
select country, region, city from HR.Employees
union all
select country, region, city from Sales.Customers

--UNION DISTINCT删除重复记录, DISTINCT必须隐式隐含
select country, region, city from HR.Employees
union 
select country, region, city from Sales.Customers

--6.2 INTERSECT(交集)集合运算
--INTERSECT隐含DISTINCT
select country, region, city from HR.Employees
intersect
select country, region, city from Sales.Customers

--集合运算对行进行比较时,认为两个null值相等

--INTERSECT ALL集合运算
--SQL Server2008没有实现这种运算

--6.3 EXCEPT(差集)集合运算
--EXCEPT集合运算隐含DISTINCT
--EXCEPT集合运算在逻辑上先删除两个输入多集中的重复行(把多集转换成集合)，
--然后返回只在第一个集合中出现，而不在第二个集合中出现的所有行。
select country, region, city from HR.Employees
except
select country, region, city from Sales.Customers

select country, region, city from Sales.Customers
except
select country, region, city from HR.Employees

--EXCEPT ALL集合运算
--SQL Server2008没有实现这种运算

--6.4 集合运算的优先级
--INTERSECT运算比UNION和EXCEPT运算的优先级高,而UNION和EXCEPT的优先级相等.
select country, region, city from Production.Suppliers
except
select country, region, city from HR.Employees
intersect 
select country, region, city from Sales.Customers

--6.5 避开不支持的逻辑查询处理
select country, COUNT(*) as numlocations
from(select country, region, city from HR.Employees
union
select country, region, city from Sales.Customers) as U
group by country;

--不能在涉及集合运算的单个查询中指定order by子句
--返回雇员ID为3或5的雇员最近处理过的两个订单
select empid, orderid, orderdate
from(select top(2) empid, orderid, orderdate
from Sales.Orders
where empid=3
order by orderdate desc, orderid desc) as D1
union all
select empid, orderid, orderdate
from(select top(2) empid, orderid, orderdate
from Sales.Orders
where empid=5
order by orderdate desc, orderid desc) as D2

--6.7 练习
--1. 返回生成一个由10个数字(1-10)组成的虚拟辅助表
select 1 as n
union all
select 2 as n
union all
select 3 as n
union all
select 4 as n
union all
select 5 as n
union all
select 6 as n
union all
select 7 as n
union all
select 8 as n
union all
select 9 as n
union all
select 10 as n
--2 返回2008年1月有订单活动,而在2008年2月没有订单活动的客户和雇员
select custid, empid
from(
select custid, empid 
from Sales.Orders
where month(orderdate) = 1 and year(orderdate) = 2008
intersect
select custid, empid
from Sales.Orders
where MONTH(orderdate) <> 2 or YEAR(orderdate) <> 2008
except
select custid, empid
from Sales.Orders
where MONTH(orderdate) = 2 and YEAR(orderdate) = 2008) as d
order by custid, empid

--3 返回在2008年1月和在2008年2月都有订单活动的客户和雇员
select custid, empid
from(
select custid, empid
from Sales.Orders
where  month(orderdate) = 1 and year(orderdate) = 2008
intersect
select custid, empid
from Sales.Orders
where MONTH(orderdate) = 2 and YEAR(orderdate) = 2008) as d
order by custid, empid

--4. 返回在2008年1月和2008年2月都有订单活动,而在2007年没有订单活动的客户和雇员
select custid, empid
from(
select custid, empid
from Sales.Orders
where  month(orderdate) = 1 and year(orderdate) = 2008
intersect
select custid, empid
from Sales.Orders
where MONTH(orderdate) = 2 and YEAR(orderdate) = 2008
except
select custid, empid
from Sales.Orders
where YEAR(orderdate) = 2007
) as d
order by custid desc, empid desc

--5 保证输出的结果中 Employees表返回的行位于Suppiers表返回的行的前面
select country, region, city
from(
select 1 as mark, country, region, city
from HR.Employees
union all
select 2 as mark, country, region, city
from Production.Suppliers) as usertable
order by mark, region, country, city