【Microsoft SQL Server 2008 技术内幕：T-SQL语言基础】四、集合运算
sql2008 t-sql集合运算 (Set Operations)
UNION 并集
[*]UNION ：求并集，删除重复行（隐含DISTINCT）
[*]UNION ALL：求并集，不比较，不删除重复行
INTERSECT 交集
[*]INTERSECT ：求交集（隐含DISTINCT），删除重复行
[*]INTERSECT ALL (Sql Server不支持)：求交集，返回有重复行，但只返回重复行数较少的那个多集(参与交集运算的集合)的所有重复行。假如行R在多集A中重复出现x次，在多集B中重复出现y次，则R在结果集中出现min(x, y)次。
EXCEPT 差集，可使用外联或NOT EXISTS实现
[*]EXCEPT ：求差集（隐含DISTINCT），删除重复行
[*]EXCEPT ALL (Sql Server不支持)：求差集，返回有重复行。假如行R在第一个多集中重复出现x次，在第二个多集中重复出现y次，x>y，则R在结果集中出现x-y次。
优先级：
[*]INTERSECT > ( UNION = EXCEPT )
集合运算的限制：
[*]参与结合运算的表表达式中无法使用ORDER BY，ORDER BY只能作用于集合运算的结果。
[*]但如果要想在参与集合运算的表表达式中使用TOP来限制行数，就需要使用表表达式将包含ORDER BY的查询包装一次。这种情况下ORDER BY只为TOP提供逻辑服务，并不控制结果的排序。
-- ==================================================
-- Sql Server INTERSECT ALL workaround
-- ==================================================
with INTERSECT_ALL as (
select ROW_NUMBER() over (
partition by region, country, city
order by (select 0)
) as rownum -- only those intersection(smaller) RowNum will remain
, region
, country
, city
from HR.Employees
INTERSECT
select ROW_NUMBER() over (
partition by region, country, city
order by (select 0)
) as rownum -- only those intersection(smaller) RowNum will remain
, region
, country
, city
from Sales.Customers
)
select country, region, city
from INTERSECT_ALL
-- ==================================================
-- Sql Server EXCEPT ALL workaround
-- ==================================================
with EXCEPT_ALL as (
select ROW_NUMBER() over (
partition by region, country, city
order by (select 0)
) as rownum -- intersection(smaller) RowNum will be removed
, region
, country
, city
from HR.Employees
EXCEPT
select ROW_NUMBER() over (
partition by region, country, city
order by (select 0)
) as rownum -- intersection(smaller) RowNum will be removed
, region
, country
, city
from Sales.Customers
)
select country, region, city
from EXCEPT_ALL
-- ==================================================
-- Using ORDER BY inside set operation
-- ==================================================
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
页:
[1]