select b.ID,b.SaleDate,a.Quantity
from (select distinct id from b ) c left join a on 1=1
left join b on a.id=b.id and a.saledate=b.dates
from (select distinct id from b ) c left join a on 1=1
left join b on a.id=b.id and a.saledate=b.dates
insert into A values ('2005-5-10 08:00:00')
insert into A values ('2005-5-11 08:00:00')
insert into A values ('2005-5-12 08:00:00')
insert into A values ('2005-5-13 08:00:00')
insert into A values ('2005-5-14 08:00:00')
insert into A values ('2005-5-15 08:00:00')
create table B (ID int,SaleDate datetime,Quantity int) insert into B values (1,'2005-5-10 08:00:00',20)
insert into B values (1,'2005-5-11 08:00:00',30)
insert into B values (1,'2005-5-12 08:00:00',23) insert into B values (2,'2005-5-12 08:00:00',40)
insert into B values (2,'2005-5-13 08:00:00',20)
insert into B values (2,'2005-5-15 08:00:00',25)
select C.ID,A.Dates, B.Quantity
from A
full outer join (select distinct ID from B)As C
on 1>0
left join B on A.Dates=B.SaleDate and C.ID=B.IDdrop table A
drop table B/*ID Dates Quantity
----------- ------------------------------------------------------ -----------
1 2005-05-09 08:00:00.000 NULL
1 2005-05-10 08:00:00.000 20
1 2005-05-11 08:00:00.000 30
1 2005-05-12 08:00:00.000 23
1 2005-05-13 08:00:00.000 NULL
1 2005-05-14 08:00:00.000 NULL
1 2005-05-15 08:00:00.000 NULL
2 2005-05-09 08:00:00.000 NULL
2 2005-05-10 08:00:00.000 NULL
2 2005-05-11 08:00:00.000 NULL
2 2005-05-12 08:00:00.000 40
2 2005-05-13 08:00:00.000 20
2 2005-05-14 08:00:00.000 NULL
2 2005-05-15 08:00:00.000 25
*/
Create Table A (Dates datetime)Create table B (ID int,SaleDate datetime,Quantity int)--插入数据
Insert into A values ('2005-5-9 08:00:00')
Insert into A values ('2005-5-10 08:00:00')
Insert into A values ('2005-5-11 08:00:00')
Insert into A values ('2005-5-12 08:00:00')
Insert into A values ('2005-5-13 08:00:00')
Insert into A values ('2005-5-14 08:00:00')
Insert into A values ('2005-5-15 08:00:00') Insert into B values (1,'2005-5-10 08:00:00',20)
Insert into B values (1,'2005-5-11 08:00:00',30)
Insert into B values (1,'2005-5-12 08:00:00',23) Insert into B values (2,'2005-5-12 08:00:00',40)
Insert into B values (2,'2005-5-13 08:00:00',20)
Insert into B values (2,'2005-5-15 08:00:00',25)
GO
--测试
Select C.ID,A.Dates,B.Quantity
from A Left Join B
On A.Dates=B.SaleDate
Full Join (Select Distinct ID from B) C On 1=1
Order By C.ID,A.Dates
--删除测试环境
Drop Table A,B
--结果
/*
ID Dates Quantity
1 2005-05-09 08:00:00.000 NULL
1 2005-05-10 08:00:00.000 20
1 2005-05-11 08:00:00.000 30
1 2005-05-12 08:00:00.000 23
1 2005-05-12 08:00:00.000 40
1 2005-05-13 08:00:00.000 20
1 2005-05-14 08:00:00.000 NULL
1 2005-05-15 08:00:00.000 25
2 2005-05-09 08:00:00.000 NULL
2 2005-05-10 08:00:00.000 20
2 2005-05-11 08:00:00.000 30
2 2005-05-12 08:00:00.000 23
2 2005-05-12 08:00:00.000 40
2 2005-05-13 08:00:00.000 20
2 2005-05-14 08:00:00.000 NULL
2 2005-05-15 08:00:00.000 25
*/
C.ID,
C.Dates,
B.Quantity
from
(select distinct B.ID,A.Dates from B cross join A) C
left join
B
on
C.ID = B.ID and C.Dates = B.SaleDate