A表:
A B C
001 JJ100 100
002 JJ200 150
004 JJ100 150
001 JJ200 200
005 JJ200 80B表:
A B E F
001 JJ100 50 60
002 JJ200 100 80
003 JJ100 200 500
004 JJ100 300 100
007 JJ200 100 100 用SQL语句怎么得出如下结果(按A,B分组):
A B C E F
001 JJ100 100 50 60
001 JJ200 200
002 JJ200 150 100 80
003 JJ100 200 500
004 JJ100 150 300 100
005 JJ200 80
007 JJ200 100 100 谢谢!~
A B C
001 JJ100 100
002 JJ200 150
004 JJ100 150
001 JJ200 200
005 JJ200 80B表:
A B E F
001 JJ100 50 60
002 JJ200 100 80
003 JJ100 200 500
004 JJ100 300 100
007 JJ200 100 100 用SQL语句怎么得出如下结果(按A,B分组):
A B C E F
001 JJ100 100 50 60
001 JJ200 200
002 JJ200 150 100 80
003 JJ100 200 500
004 JJ100 150 300 100
005 JJ200 80
007 JJ200 100 100 谢谢!~
from A表 t1 full join B表 t2 on t1.A = t2.A and t1.B = t2.B
select '001' as A, 'JJ100' as B, 100 as C
into A表
union select '002', 'JJ200', 150
union select '004', 'JJ100', 150
union select '001', 'JJ200', 200
union select '005', 'JJ200', 80
if object_id('B表') is not null drop table B表
select '001' as A, 'JJ100' as B, 50 as E, 60 as F
into B表
union select '002', 'JJ200', 100, 80
union select '003', 'JJ100', 200, 500
union select '004', 'JJ100', 300, 100
union select '007', 'JJ200', 100, 100
------------------------------------------------------
select isnull(t1.A, t2.A) as A, isnull(t1.B, t2.B) as B, t1.C, t2.E, t2.F
from A表 t1 full join B表 t2 on t1.A = t2.A and t1.B = t2.B
order by isnull(t1.A, t2.A), isnull(t1.B, t2.B)
/*
A B C E F
---- ----- ----------- ----------- -----------
001 JJ100 100 50 60
001 JJ200 200 NULL NULL
002 JJ200 150 100 80
003 JJ100 NULL 200 500
004 JJ100 150 300 100
005 JJ200 80 NULL NULL
007 JJ200 NULL 100 100*/
------------------------------------------------------
drop table A表
drop table B表