create table A
(
date datetime not null,
num1 int not null
)insert into a values('2009-7-1',10)
insert into a values('2009-7-3',15)create table B
(
date datetime not null,
num2 int not null
)insert into b values('2009-7-2',12)
insert into b values('2009-7-3',8)--我想显示这种结果怎么做
Date Num1 Num2
2009-7-1 10 0
2009-7-2 0 12
2009-7-3 15 8
(
date datetime not null,
num1 int not null
)insert into a values('2009-7-1',10)
insert into a values('2009-7-3',15)create table B
(
date datetime not null,
num2 int not null
)insert into b values('2009-7-2',12)
insert into b values('2009-7-3',8)--我想显示这种结果怎么做
Date Num1 Num2
2009-7-1 10 0
2009-7-2 0 12
2009-7-3 15 8
from (select date ,num as num1 ,0 as num2
from a
union all
select date,0,num from b ) c
group by convert(char(10),date,120)
from a
full join b
on a.date=b.date
order by 1
/*
date num1 num2
----------------------- ----------- -----------
2009-07-01 00:00:00.000 10 0
2009-07-02 00:00:00.000 0 12
2009-07-03 00:00:00.000 15 8(3 行受影响)
*/
from a
full join b
on a.date=b.date
order by 1
/*
date num1 num2
------------------------------ ----------- -----------
2009-07-01 10 0
2009-07-02 0 12
2009-07-03 15 8(3 行受影响)*/
select Case isnull(cast(a.date as nvarchar(10)),'null') when 'null' then b.date else a.date end as date,
CASE isnull(cast(NUM1 as nvarchar(10)),'null') when 'null' then 0 else NUM1 end as num1,
CASE isnull(cast(NUM2 as nvarchar(10)),'null') when 'null' then 0 else NUM2 end as num2
from a full join b
on a.date=b.date