select A.时间A,A.奖金A,B.奖金B from A,B where A.时间A=B.时间B
union select A.时间A,A.奖金A,B.奖金B from A left outer join B on A.时间A=B.时间B
union select B.时间A,A.奖金A,B.奖金B from A right outer join B on A.时间A=B.时间B
union select A.时间A,A.奖金A,B.奖金B from A left outer join B on A.时间A=B.时间B
union select B.时间A,A.奖金A,B.奖金B from A right outer join B on A.时间A=B.时间B
go
create table A(时间A datetime,奖金A int)
insert into A
select '2007-04-09',1500
union all select '2007-04-10',1000
union all select '2007-04-11',500
create table B(时间B datetime,奖金B int)
insert into B
select '2007-04-10',500
union all select '2007-04-12',1000select isnull(时间A,时间B) as '时间',奖金A,奖金B
from A
full outer join B on A.时间A=B.时间B
/*
时间 奖金A 奖金B
------------------------------------------------------ ----------- -----------
2007-04-09 00:00:00.000 1500 NULL
2007-04-10 00:00:00.000 1000 500
2007-04-11 00:00:00.000 500 NULL
2007-04-12 00:00:00.000 NULL 1000(所影响的行数为 4 行)
*/
--用FULL JOIN
Select
IsNull(A.[时 间A], B.[时 间B]) As [时 间],
A.奖金A ,
B.奖金B
From
A
Full Join
B
On A.[时 间A] = B.[时 间B]
insert into A
select '2007-04-09',1500
union all select '2007-04-10',1000
union all select '2007-04-11',500
create table B([时 间B] datetime,奖金B int)
insert into B
select '2007-04-10',500
union all select '2007-04-12',1000
GO
--用FULL JOIN
Select
IsNull(A.[时 间A], B.[时 间B]) As [时 间],
A.奖金A ,
B.奖金B
From
A
Full Join
B
On A.[时 间A] = B.[时 间B]
GO
Drop Table A, B
--Result
/*
时 间 奖金A 奖金B
2007-04-09 00:00:00.000 1500 NULL
2007-04-10 00:00:00.000 1000 500
2007-04-11 00:00:00.000 500 NULL
2007-04-12 00:00:00.000 NULL 1000
*/
select A.时间A,A.奖金A,B.奖金B from A,B where A.时间A=B.时间B
union select A.时间A,A.奖金A,B.奖金B from A left outer join B on A.时间A=B.时间B
union select B.时间B,A.奖金A,B.奖金B from A right outer join B on A.时间A=B.时间B
Select
IsNull(A.[时 间A], B.[时 间B]) As [时 间],
IsNull(A.奖金A, 0) 奖金A,
IsNull(B.奖金B, 0) As 奖金B
From
A
Full Join
B
On A.[时 间A] = B.[时 间B]
create table B(时间B datetime,奖金B int);insert into A
select '2007-04-09',1500
union select '2007-04-10',1000
union select '2007-04-11',500insert into B
select '2007-04-10',500
union select '2007-04-12',1000select * from A
select * from B
select A.时间A,A.奖金A,B.奖金B from A,B where A.时间A=B.时间B
union select A.时间A,A.奖金A,B.奖金B from A left outer join B on A.时间A=B.时间B
union select B.时间B,A.奖金A,B.奖金B from A right outer join B on A.时间A=B.时间B