联接查询

---------------------------------------------------------------------------------------------------------
--联接查询
---------------------------------------------------------------------------------------------------------
--联接查询
--交叉联接
--内联接
--外联接

---------------------------------------------------------------------------------------------------------
--表联接【完成】
---------------------------------------------------------------------------------------------------------
--定义:将多张表合并成一张表，将列合并
--交叉联接 cross join
--内联接 inner join\join on
--外联接 left\right\full join
--特殊的联接实例:组合联接\不等联接\多表联接（内联接）


--语法：
--交叉联接
select * 
from table1
cross join table2


--内联接
select * 
from table1 as t1
inner join table2 as t2
on t1.id=t2.id


--外联接
select * 
from table1 as t1
left join table2 as t2
on t1.id=t2.id


--拓展：(组合联接)
select * 
from table1 as t1
left join table2 as t2
on t1.id=t2.id and t2.orderdate='20140223'




--连续数据表，得到一个1000的列值，(一般应用于数据优化,可以生成数字表)
----------------------------------------------------------------------------
--举例:
--创建表(一个字段，0-9十行)
create table contiNum
(
num int
)
insert into contiNum(num) values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
--思路
select *
from contiNum as cnt1
cross join
contiNum as cnt2
order by cnt1.num,cnt2.num
--得到100以内的值
select 
(cnt3.num*100+cnt2.num*10+cnt1.num+1) as digit
from contiNum as cnt1
cross join contiNum as cnt2
cross join contiNum as cnt3
order by digit
--说明:把每个实例的数字乘以它代表的10的幂,在把结果加起来,最后加1,就得到相应的整数序列






--外联接高级知识
------------------------------------------------------------------------------------
--例子：确保对于2016年1月1日到2008年12月31日之间的每个日期至少在输出结果中出现一行
--创建一个Nums表插入从1到100000的数字行
set nocount on --表示不返回计数(影响行数)
use tempdb
if OBJECT_ID('Nums','U') is not null drop table Nums;
create table Nums
(n int not null primary key)
declare @i int =1;
begin tran
while @i<=100000
begin
insert into dbo.Nums values(@i);
set @i=@i+1;
end
commit tran
set nocount off;
--查询
select * from dbo.Nums
--得到日期的某个区间,前提是先建一个数字表
select DATEADD(day,n-1,'20060101')as orderdate
from dbo.Nums
where n<=DATEDIFF(day,'20060101','20081231')+1
order by orderdate
--与Orders表外联接
select DATEADD(day,dbo.Nums.n-1,'20060101')as orderdate,
o.orderid,o.custid,o.empid
from dbo.Nums
left join Sales.Orders as o
on DATEADD(day,n-1,'20060101')=o.orderdate
where Nums.n<=DATEDIFF(day,'20060101','20081231')+1
order by orderdate