2023年11月27日发(作者：)
MicrosoftSQLServer2008技术内幕：T-SQL语⾔基础（集合运算）
T-SQL语⾔基础
六、集合运算
6.1 集合运算的特点
6.1.1 集合查询不允许包含order by
集合是⽆序的
集合运算涉及的两个查询不能包含order by⼦句
6.1.2 null相等
对⾏进⾏⽐较时，集合运算认为两个null相等
6.2 all 和 distinct
6.2.1 all
在进⾏集合运算时，重复⾏会参与运算
6.2.2 distinct
在进⾏集合运算时，重复⾏不参与运算
6.3 union（并集）集合运算
6.3.1 union all
select country, region, city from hr.employees
union all
select country, region, city from sales.customers;
6.3.2 union distinct
即使不指定distinct，union运算默认重复⾏不参与运算
select country, region, city from hr.employees
union
select country, region, city from sales.customers;
6.4 intersect（交集）集合运算
6.4.1 intersect distinct
select country, region, city from hr.employees
intersect
select country, region, city from sales.customers;
与intersect distinct具有相同效果
inner join
exists
以上都会过滤null
6.4.2 intersect all
intersect all
重复⾏也参与计算
如果重复⾏在交集中，则最终结果中重复⾏的数量等于两个集合中相应的重复⾏的数量的最⼩值
集合a中有x⾏，集合b中有y⾏，则为min(x,y)
SQL Server2008暂未实现intersect all
替代⽅案
引⼊⾏号，将重复的变为不重复
over( partition by … order by (select 常量) )
没有强烈的排序要求，⾃然排序
select empid,city,
ROW_NUMBER() over(partition by city
order by (select 0))
as rownum
from HR.Employees;
with intersect_all
as
(
select country, region, city ,
row_number() over(partition by country, region,city
order by (select 0) )
as rownum
from hr.employees
intersect
select country, region, city ,
row_number() over(partition by country, region,city
order by (select 0) )
as rownum
from sales.customers;
)
select country, region, city from intersect_all;
6.5 except（差集）集合运算
6.5.1 except distinct
A except B
表⽰在集合A中出现，但不在集合B中出现
select country, region, city from hr.employees
except
select country, region, city from sales.customers;
与except distinct具有相同效果
外联接加过滤条件，只取外部⾏
not exists
以上都会过滤null
6.5.2 except all
except all
重复⾏也参与计算
如果重复⾏在差集中，则最终结果中重复⾏的数量等于两个集合中相应的重复⾏的数量的差值
集合a中有x⾏，集合b中有y⾏，则为x-y
SQL Server2008暂未实现except all
替代⽅案
with except_all
as
(
select country, region, city ,
row_number() over(partition by country, region,city
order by (select 0) )
as rownum
from hr.employees
except
select country, region, city ,
row_number() over(partition by country, region,city
order by (select 0) )
as rownum
from sales.customers;
)
select country, region, city from except_all;
6.6 集合运算的优先级
intersect优先级最⾼
union和except优先级相等
6.7 避开不⽀持的逻辑查询处理
6.7.1 对集合运算结果的操作只允许使⽤order by
使⽤表表达式，将集合运算结果作为内部查询
with union_distinct
as
(
select country, region, city from hr.employees
union
select country, region, city from sales.customers
)
select country, region, city from union_distinct
group by country;
6.7.2 参与集合运算的单个查询不⽀持order by
使⽤表表达式，将单个查询作为内部查询
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
发布评论