Select A.Dates,B.ID,C.nums from #t3 A
Full Join (Select Distinct ID from #t) B On 1=1
Left Join #t C
On A.Dates=C.Dates And B.ID=C.ID--结果
/*
Dates ID nums
2005-05-20 00:00:00.000 ccc 2
2005-05-23 00:00:00.000 ccc 3
2005-05-25 00:00:00.000 ccc 5
2005-05-20 00:00:00.000 sss 0
2005-05-23 00:00:00.000 sss NULL
2005-05-25 00:00:00.000 sss NULL
*/
Full Join (Select Distinct ID from #t) B On 1=1
Left Join #t C
On A.Dates=C.Dates And B.ID=C.ID--结果
/*
Dates ID nums
2005-05-20 00:00:00.000 ccc 2
2005-05-23 00:00:00.000 ccc 3
2005-05-25 00:00:00.000 ccc 5
2005-05-20 00:00:00.000 sss 0
2005-05-23 00:00:00.000 sss NULL
2005-05-25 00:00:00.000 sss NULL
*/
create table #t3(Dates datetime)
insert into #t values ('2005-5-20','sss',0)
insert into #t values ('2005-5-20','ccc',2)
insert into #t values ('2005-5-23','ccc',3)
insert into #t values ('2005-5-25','ccc',5)
insert into #t3 values ('2005-5-20')
insert into #t3 values ('2005-5-23')
insert into #t3 values ('2005-5-25')select *,nums=(select nums from #t where ID=A.ID and Dates=#t3.Dates)
from #t3
left join (select distinct ID from #t)A
on 1>0 order by ID desc,Dates drop table #t
drop table #t3--结果
/*Dates ID nums
------------------------------------------------------ -------------------- -----------
2005-05-20 00:00:00.000 sss 0
2005-05-23 00:00:00.000 sss NULL
2005-05-25 00:00:00.000 sss NULL
2005-05-20 00:00:00.000 ccc 2
2005-05-23 00:00:00.000 ccc 3
2005-05-25 00:00:00.000 ccc 5
*/
Dates ID nums
---------------------------- ----------- -----------
2005-05-20 00:00:00.000 sss 0
2005-05-23 00:00:00.000 sss NULL
2005-05-25 00:00:00.000 sss NULL
2005-05-20 00:00:00.000 ccc 2
2005-05-23 00:00:00.000 ccc 3
2005-05-25 00:00:00.000 ccc 5