select a.*,[E]=isnull(b.E,''),[F]=isnull(b.F,''),[G]=isnull(b.G,'') from A a left join B b on a.日期=b.日期
select ISNULL(A.日期,B.日期), A.A, A.B, A.C, B.E, B.F, B.G from A full join B on A.日期=B.日期
if object_id('tableA') is not null drop table tableA select '20061014' as 日期, 1 as A, 3 as B, 4 as C into tableA union select '20061014', 2, 2, 1 union select '20061015', 3, 1, 6 if object_id('tableB') is not null drop table tableB select '20061014' as 日期, 3 as E, 5 as F, 7 as G into tableB union select '20061015', 4, 6, 8 ---------------------------------------------- --假设ABCEFG的值都不超过10000 select isnull(a.日期, b.日期), a.A, a.B, a.C, b.E, b.F, b.G from (select *, (select count(1) from tableA where 日期 = aa.日期 and (A * 10000 + B) * 1000 + C < (aa.A * 10000 + aa.B) * 1000 + aa.C) as I from tableA aa) a full join (select *, (select count(1) from tableB where 日期 = bb.日期 and (E * 10000 + F) * 1000 + G < (bb.E * 10000 + bb.F) * 1000 + bb.G) as I from tableB bb) b on a.日期 = b.日期 and a.I = b.I /* 日期 A B C E F G 20061014 1 3 4 3 5 7 20061014 2 2 1 NULL NULL NULL 20061015 3 1 6 4 6 8 */ ---------------------------------------------- drop table tableA drop table tableB
if object_id('tableA') is not null drop table tableA select '20061014' as 日期, 1 as A, 3 as B, 4 as C into tableA union select '20061014', 2, 2, 1 union select '20061015', 3, 1, 6 if object_id('tableB') is not null drop table tableB select '20061014' as 日期, 3 as E, 5 as F, 7 as G into tableB union select '20061015', 4, 6, 8 ---------------------------------------------- select isnull(a.日期, b.日期) as 日期, a.A, a.B, a.C, b.E, b.F, b.G from (select *, (select count(1) from tableA where 日期 = aa.日期 and A < aa.A or (A = aa.A and B < aa.B) or (A = aa.A and B = aa.B and C < aa.C)) as I from tableA aa) a full join (select *, (select count(1) from tableB where 日期 = bb.日期 and E < bb.E or (E = bb.E and F < bb.F) or (E = bb.E and F = bb.F and G < bb.G)) as I from tableB bb) b on a.日期 = b.日期 and a.I = b.I /* 日期 A B C E F G 20061014 1 3 4 3 5 7 20061014 2 2 1 NULL NULL NULL 20061015 3 1 6 4 6 8 */ ---------------------------------------------- drop table tableA drop table tableB
谢了 YiZhiNet(九斤半) 我的join 前面没有加full,所以查出来的信息就少了点,
select ISNULL(A.日期,B.日期), A.A, A.B, A.C, B.E, B.F, B.G from A full join B on A.日期=B.日期
ISNULL(A.日期,B.日期),
A.A,
A.B,
A.C,
B.E,
B.F,
B.G
from A
full join B on A.日期=B.日期
select '20061014' as 日期, 1 as A, 3 as B, 4 as C
into tableA
union select '20061014', 2, 2, 1
union select '20061015', 3, 1, 6
if object_id('tableB') is not null drop table tableB
select '20061014' as 日期, 3 as E, 5 as F, 7 as G
into tableB
union select '20061015', 4, 6, 8
----------------------------------------------
--假设ABCEFG的值都不超过10000
select isnull(a.日期, b.日期), a.A, a.B, a.C, b.E, b.F, b.G
from (select *, (select count(1)
from tableA
where 日期 = aa.日期
and (A * 10000 + B) * 1000 + C < (aa.A * 10000 + aa.B) * 1000 + aa.C) as I
from tableA aa) a
full join (select *, (select count(1)
from tableB
where 日期 = bb.日期
and (E * 10000 + F) * 1000 + G < (bb.E * 10000 + bb.F) * 1000 + bb.G) as I
from tableB bb) b on a.日期 = b.日期 and a.I = b.I
/*
日期 A B C E F G
20061014 1 3 4 3 5 7
20061014 2 2 1 NULL NULL NULL
20061015 3 1 6 4 6 8
*/
----------------------------------------------
drop table tableA
drop table tableB
select '20061014' as 日期, 1 as A, 3 as B, 4 as C
into tableA
union select '20061014', 2, 2, 1
union select '20061015', 3, 1, 6
if object_id('tableB') is not null drop table tableB
select '20061014' as 日期, 3 as E, 5 as F, 7 as G
into tableB
union select '20061015', 4, 6, 8
----------------------------------------------
select isnull(a.日期, b.日期) as 日期, a.A, a.B, a.C, b.E, b.F, b.G
from (select *, (select count(1)
from tableA
where 日期 = aa.日期
and A < aa.A or (A = aa.A and B < aa.B) or (A = aa.A and B = aa.B and C < aa.C)) as I
from tableA aa) a
full join (select *, (select count(1)
from tableB
where 日期 = bb.日期
and E < bb.E or (E = bb.E and F < bb.F) or (E = bb.E and F = bb.F and G < bb.G)) as I
from tableB bb) b on a.日期 = b.日期 and a.I = b.I
/*
日期 A B C E F G
20061014 1 3 4 3 5 7
20061014 2 2 1 NULL NULL NULL
20061015 3 1 6 4 6 8
*/
----------------------------------------------
drop table tableA
drop table tableB
我的join 前面没有加full,所以查出来的信息就少了点,
ISNULL(A.日期,B.日期),
A.A,
A.B,
A.C,
B.E,
B.F,
B.G
from A
full join B on A.日期=B.日期