34,576
社区成员
发帖
与我相关
我的任务
分享
create table a(YGBH varchar(10),YLBX_JF varchar(10),GJJ_JF varchar(10)
,SJ datetime
)
insert into a
select '001', '100', '200', '2014-3-16' union all
select '001', '101', '201', '2014-3-17' union all
select '001', '100', '202', '2014-4-16'
create table b(
YGBH varchar(10),
YLBX_KF varchar(10), GJJ_KF varchar(10),SJ datetime,
YLBX_BJ int, GJJ_BJ int
)
insert into b
select '001', '100', '200', '2014-3-16',0,0 union all
select '001', '101', '201', '2014-3-18',0,10
go
select ISNULL(a.YGBH,b.YGBH) YGBH,
b.YLBX_KF YLBX_KF,
b.GJJ_KF GJJ_KF,
ISNULL(a.SJ,b.SJ) SJ,
b.YLBX_BJ,
b.GJJ_BJ,
a.YLBX_JF,
a.GJJ_JF
from a
full join b
on b.YGBH = a.YGBH and
b.YLBX_KF = a.YLBX_JF and
b.GJJ_KF = a.GJJ_JF and
b.SJ = a.SJ
/*
YGBH YLBX_KF GJJ_KF SJ YLBX_BJ GJJ_BJ YLBX_JF GJJ_JF
001 100 200 2014-03-16 00:00:00.000 0 0 100 200
001 NULL NULL 2014-03-17 00:00:00.000 NULL NULL 101 201
001 NULL NULL 2014-04-16 00:00:00.000 NULL NULL 100 202
001 101 201 2014-03-18 00:00:00.000 0 10 NULL NULL
*/