SQL语句 .

求一个SQL语句 ...
表1
FCY BNAM BNUM ITMDES ITMNUM FQTY
A01 AAA A001 AXXXX 100111 23
A01 AAA A001 BXXXX 200111 50

表2
FCY BNAM BNUM ITMDES ITMNUM TQTY
A01 AAA A001 BXXXX 200111 25
A01 AAA A001 CXXXX 300111 80

要达到的效果:
FCY BNAM BNUM ITMDES ITMNUM FQTY TQTY
A01 AAA A001 AXXXX 100111 23 0
A01 AAA A001 BXXXX 200111 50 25
A01 AAA A001 CXXXX 300111 0 80

请问这两个表 要怎么连接才能达到上面的效果,这FCY BNAM BNUM ITMDES ITMNUM FQTY TQTY几个字段都要显示

[解决办法]

SQL code

select FCY,BNAM,BNUM,ITMDES,ITMNUM,sum(FQTY) FQTY,sum(TQTY) TQTY from (select FCY,BNAM,BNUM,ITMDES,ITMNUM,FQTY,TQTY=convert(float,0) from 表1union all select FCY,BNAM,BNUM,ITMDES,ITMNUM,0,TQTY from 表2) tgroup by FCY,BNAM,BNUM,ITMDES,ITMNUM;
[解决办法]
SQL code
if object_id(N'[表1]') is not null drop table [表1]gocreate table [表1](FCY varchar(10),BNAM varchar(10), BNUM varchar(10), ITMDES varchar(10), ITMNUM varchar(10), FQTY int)goinsert into [表1]select 'A01', 'AAA', 'A001', 'AXXXX', '100111', 23 union allselect 'A01', 'AAA', 'A001', 'BXXXX', '200111', 50goif object_id(N'[表2]') is not null drop table [表2]gocreate table [表2](FCY varchar(10),BNAM varchar(10), BNUM varchar(10), ITMDES varchar(10), ITMNUM varchar(10), TQTY int)goinsert into [表2]select 'A01', 'AAA', 'A001', 'BXXXX', '200111', 25 union allselect 'A01', 'AAA', 'A001', 'CXXXX', '300111', 80goselect FCY, BNAM, BNUM, ITMDES, ITMNUM,sum(FQTY) FQTY,sum(TQTY) TQTY from(    select FCY, BNAM, BNUM, ITMDES, ITMNUM,FQTY,0 as TQTY from [表1]     union all    select FCY, BNAM, BNUM, ITMDES, ITMNUM,0 as FQTY,TQTY from [表2] ) tgroup by FCY, BNAM, BNUM, ITMDES, ITMNUM/*(2 row(s) affected)(2 row(s) affected)FCY        BNAM       BNUM       ITMDES     ITMNUM     FQTY        TQTY---------- ---------- ---------- ---------- ---------- ----------- -----------A01        AAA        A001       AXXXX      100111     23          0A01        AAA        A001       BXXXX      200111     50          25A01        AAA        A001       CXXXX      300111     0           80(3 row(s) affected)*/