日期:2014-05-17 浏览次数:20577 次
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 表1 union all select FCY,BNAM,BNUM,ITMDES,ITMNUM,0,TQTY from 表2) t group by FCY,BNAM,BNUM,ITMDES,ITMNUM;
------解决方案--------------------
if object_id(N'[表1]') is not null drop table [表1] go create table [表1](FCY varchar(10),BNAM varchar(10), BNUM varchar(10), ITMDES varchar(10), ITMNUM varchar(10), FQTY int) go insert into [表1] select 'A01', 'AAA', 'A001', 'AXXXX', '100111', 23 union all select 'A01', 'AAA', 'A001', 'BXXXX', '200111', 50 go if object_id(N'[表2]') is not null drop table [表2] go create table [表2](FCY varchar(10),BNAM varchar(10), BNUM varchar(10), ITMDES varchar(10), ITMNUM varchar(10), TQTY int) go insert into [表2] select 'A01', 'AAA', 'A001', 'BXXXX', '200111', 25 union all select 'A01', 'AAA', 'A001', 'CXXXX', '300111', 80 go select 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] ) t group 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 0 A01 AAA A001 BXXXX 200111 50 25 A01 AAA A001 CXXXX 300111 0 80 (3 row(s) affected) */