第7章 联接和集合运算

7.1 联接
基本联接类型(交叉联接，内联接，外联接)
交叉联接(CROSS), 对两个表执行笛卡儿积运算。
示例一: 为每个员工，每个客户，一段时间每一天生成一个订单
if OBJECT_ID(‘dbo.MyOrders’) is not null
drop table dbo.MyOrders;
go

declare @fromdate as date=‘20190101’;
declare @enddate as date=‘20190131’;

with Orders
as(
select custid,empid,DATEADD(day,n-1,@fromdate) as orderdate
from Sales.Customers
cross join HR.Employees
cross join dbo.Nums
where n<=DATEDIFF(day,@fromdate,@enddate)+1
)
select ROW_NUMBER() over(order by (select 0)) as id,
custid,empid,orderdate
into dbo.MyOrders
from Orders;

select * from dbo.MyOrders order by id

示例二: 用交叉联接获得聚合值进行查询，可以获得好的性能
with Aggs as
(select sum(val) as sumval,avg(val) as avgval from Sales.OrderValues)
select orderdate,orderid,val,
cast(val/sumval * 100. as numeric(5,2)) as pct,
cast(val - avgval as numeric(12,2)) as diff
from Sales.OrderValues
cross join Aggs

内联接(Inner)
Inner Join 不涉及添加外部行的过程，在Inner Join的on 子句还是在where子句中指定逻辑表达式没有区别。 实践中，通常表之间匹配属性的删选器位于on子句，只对一个表的属性进行删选的筛选器放在Where子句。

外联接(Outer)
返回两个表中按一定条件匹配的行，以及保留表中不能被匹配的行

7.1.3 其他的联接分类
自联接
同一个表的不同实例直接的联接
不等联接
不等联接使用子查询计算行号的例子，见第6章
同时也可以使用“扩展-压缩”计算行号，示例如下：
select a.orderid,a.custid,a.empid,count(*) as rn
from Sales.Orders a
inner join Sales.Orders b on b.orderid<=a.orderid
group by a.orderid,a.custid,a.empid

多联接
三个或者三个以上表进行联接查询
半联接(效率高)
根据一个表中是否存在匹配行，而从另一个表中返回行的联接。左半联接从左表返回值，。。。
联接运算符中会有Left Semi Join(Semi）的字样
反半联接,类似Left Anti Semi Join的字样
实现半联接的方法，内联接 (属性选择从一对多关系的1方，并且使用Distinct关键字)， 带有子查询的Exists 和In子句，Intersect集合运算
select distinct a.custid,a.companyname
from Sales.Customers a
inner join Sales.Orders b on a.custid=b.custid
where country=‘Spain’

select custid,companyname
from Sales.Customers a
where exists(select * from Sales.Orders b where b.custid=a.custid)
and country=‘Spain’

反半联接的方法，外联接(值筛选外部行)，带有Not Exists或者Not In 谓词
select custid,companyname
from Sales.Customers a
where not exists(select * from Sales.Orders b where b.custid=a.custid)
and country=‘Spain’

7.1.4 上一年的滑动累计
以下查询，返回每个月份的上一年度的年度活动订单总数，适用于月份没有间断的情况
SELECT convert(char(6),dateadd(month,-11,a.ordermonth),112) as frommonth,
convert(char(6),a.ordermonth,112) as tomonth,sum(b.val) as yearlyval
FROM dbo.MonthlyOrders a
inner join dbo.MonthlyOrders b on b.ordermonth between DATEADD(month,-11,a.ordermonth) and a.ordermonth
group by a.ordermonth
having count(*)=12

对于无法保证月份序列不间断的情况，可以通过一个辅助表生成月份序列，再通过左外联接，联接数据表进行计算，例如:
declare @firstmonth as date=‘20061201’;
declare @lastmonth as date=‘20081201’;
declare @monthstrailing as int =11;

with Months
as
(
select DATEADD(month,n-1-@monthstrailing,@firstmonth) as frommonth,
DATEADD(month,n-1,@firstmonth) as tomonth, n
from dbo.Nums
where n<=DATEDIFF(month,@firstmonth,@lastmonth)+1
)
select frommonth, tomonth,count() as numMonths,sum(val) as totalvalue
from Months a
left join MonthlyOrders b on b.ordermonth between a.frommonth and a.tomonth
group by frommonth, tomonth
having count()=12
order by frommonth, tomonth

7.1.5 联接算法
SQL Server支持三种联接算法: 嵌套循环(nested loopps), 合并(merge), 哈希(Hash)
嵌套循环(nested loopps)，将要联接的表分内表和外表，对外表的每一行，在内表执行一个匹配查询，如此循环。 此时外表叫小，内表联接列存在合适索引，性能越好

合并(Merge) , 合并联接要求输入的两个表在联接列是有序的，要求至少有一个等值联接谓词，算法先从左表返回一行，检查返回右表对应匹配行，再从左表返回下一行。对于联接列事先排序的列，左右表联接裂伤都建索引，效率很好。

哈希(Hash)，对于大规模输入，通常是在数据仓库中比较有效。 OLTP中出现，可能是缺少好的索引。
左右表会分别作为生成输入和探测输入，对生成输入应用一个哈希函数，生成组数固定，组大小尽量一致的分布桶。 然后对探测输入一侧应用这个Hash表。

7.1.6 拆分元素

–准备数据
IF OBJECT_ID(‘dbo.Arrays’) IS NOT NULL DROP TABLE dbo.Arrays;
CREATE TABLE dbo.Arrays
(
arrid VARCHAR(10) NOT NULL PRIMARY KEY,
array VARCHAR(8000) NOT NULL
)
GO

INSERT INTO Arrays(arrid, array) VALUES
(‘A’, ‘20,223,2544,25567,14’),
(‘B’, ‘30,-23433,28’),
(‘C’, ‘12,10,8099,12,1200,13,12,14,10,9’),
(‘D’, ‘-4,-6,-45678,-2’);
GO

–拆分元素， 辅助表解法
–生成副本，获取元素时用到charindex函数，该函数类似Excel函数find
select arrid,cast(SUBSTRING(array,n,charindex(’,’,array+’,’,n)-n) as int) as element,
ROW_NUMBER() over(partition by arrid order by arrid) as rnt
–,array,n
from dbo.arrays a
inner join dbo.Nums b on b.n<=len(a.array)
and SUBSTRING(’,’+a.array,n,1)=’,’
order by a.arrid

–拆分元素， 递归CTE解法
–定位点成员计算第一个元素的开始，结束位置，递归成员计算下一个元素的开始，结束位置。直到找不到下一个元素时为止
–外部查询联接CTE和原表，根据开始，结束位置获取元素数据
WITH Split AS
(
SELECT arrid, 1 AS pos, 1 AS startpos,
CHARINDEX(’,’, array + ‘,’) - 1 AS endpos
FROM dbo.Arrays
WHERE DATALENGTH(array) > 0

UNION ALL

SELECT Prv.arrid, Prv.pos + 1, Prv.endpos + 2,
CHARINDEX(’,’, Cur.array + ‘,’, Prv.endpos + 2) - 1
FROM Split AS Prv
JOIN dbo.Arrays AS Cur
ON Cur.arrid = Prv.arrid
AND CHARINDEX(’,’, Cur.array + ‘,’, Prv.endpos + 2) > 0
)

SELECT A.arrid, pos,
CAST(SUBSTRING(array, startpos, endpos-startpos+1) AS INT) AS element
FROM dbo.Arrays AS A
JOIN Split AS S
ON S.arrid = A.arrid
ORDER BY arrid, pos;