求一SQL语句,已知两个表,如何通过查询合并成明细表?

/*
   已知Test1和Test2,两表合并,
   明细记录的应收金额和应付金额由表Test2 决定,
   如何生成结果Test3?

对于应收:
销售单\付款单\进货退货单 的金额为正数 我们的  应收金额在增加 +
进货单\收款单\销售退货单 的金额为负数 我们的  应收金额在减少 -

对于应付:
销售单\付款单\进货退货单 的金额为负数 我们的  应付金额在减少 -
进货单\收款单\销售退货单 的金额为正数 我们的  应付金额在增加 +



*/
-----建立测试环境------
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table [dbo].[Test1]
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table [dbo].[Test2]
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table [dbo].[Test3]
GO
CREATE TABLE [Test1] (
[日期] [smalldatetime] NULL ,
[单号] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[公司编号] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[公司名称] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[产品全名] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[单位] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[数量] [decimal](19, 4) NULL ,
[单价] [money] NULL ,
[金额] [money] NULL ,
[说明] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO
CREATE TABLE [Test2] (
[日期] [datetime] NULL ,
[公司编号] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[公司名称] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[应收金额] [money] NULL ,
[应付金额] [money] NULL ,
[期初] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[单号] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[说明] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
CREATE TABLE [Test3] (
[日期] [smalldatetime] NULL ,
[单号] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[公司编号] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[公司名称] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[产品全名] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[单位] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[数量] [decimal](19, 4) NULL ,
[单价] [money] NULL ,
[应收金额] [money] NULL ,
[应付金额] [money] NULL ,
[说明] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
Insert Test1 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,金额,说明)  Values ( '2010-08-09 00:00:00.000','JH-2010-08-09-001','G001','大龙','GODEX 1100 PLUS','台',10.0000,1600.00,16000.00,'进货单')
Insert Test1 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,金额,说明)  Values ( '2010-08-09 00:00:00.000','JH-2010-08-09-001','G001','大龙','GODEX 2100 PLUS','台',10.0000,3700.00,37000.00,'进货单')
Insert Test1 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,金额,说明)  Values ( '2010-08-09 00:00:00.000','JHTH-2010-08-09-001','G001','大龙','GODEX 2100 PLUS','台',5.0000,3700.00,18500.00,'进货退货单')
Insert Test1 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,金额,说明)  Values ( '2010-08-09 00:00:00.000','XL-2010-08-09-001','001','讯达','GODEX 1100 PLUS','台',8.0000,2000.00,16000.00,'销售单')
Insert Test1 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,金额,说明)  Values ( '2010-08-09 00:00:00.000','XL-2010-08-09-002','001','讯达','GODEX 1100 PLUS','台',3.0000,2000.00,6000.00,'销售单')
Insert Test1 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,金额,说明)  Values ( '2010-08-09 00:00:00.000','XL-2010-08-09-003','001','讯达','GODEX 1100 PLUS','台',1.0000,2000.00,2000.00,'销售单')
Insert Test1 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,金额,说明)  Values ( '2010-08-09 00:00:00.000','XL-2010-08-09-003','001','讯达','GODEX 2100 PLUS','台',1.0000,4500.00,4500.00,'销售单')
Insert Test1 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,金额,说明)  Values ( '2010-08-09 00:00:00.000','XLTH-2010-08-09-001','001','讯达','GODEX 1100 PLUS','台',3.0000,2000.00,6000.00,'销售退货单')
GO
Insert Test2 (日期,公司编号,公司名称,应收金额,应付金额,期初,单号,说明)  Values ( '2010-08-09 00:00:00.000','G001','大龙',-53000.00,53000.00,NULL,'JH-2010-08-09-001','进货单')
Insert Test2 (日期,公司编号,公司名称,应收金额,应付金额,期初,单号,说明)  Values ( '2010-08-09 00:00:00.000','G001','大龙',18500.00,-18500.00,NULL,'JHTH-2010-08-09-001','进货退货单')
Insert Test2 (日期,公司编号,公司名称,应收金额,应付金额,期初,单号,说明)  Values ( '2010-08-09 00:00:00.000','001','讯达',16000.00,-16000.00,NULL,'XL-2010-08-09-001','销售单')
Insert Test2 (日期,公司编号,公司名称,应收金额,应付金额,期初,单号,说明)  Values ( '2010-08-09 00:00:00.000','001','讯达',-6000.00,6000.00,NULL,'XLTH-2010-08-09-001','销售退货单')
Insert Test2 (日期,公司编号,公司名称,应收金额,应付金额,期初,单号,说明)  Values ( '2010-08-09 00:00:00.000','001','讯达',6000.00,-6000.00,NULL,'XL-2010-08-09-002','销售单')
Insert Test2 (日期,公司编号,公司名称,应收金额,应付金额,期初,单号,说明)  Values ( '2010-08-09 00:00:00.000','001','讯达',6500.00,-6500.00,NULL,'XL-2010-08-09-003','销售单')
Insert Test2 (日期,公司编号,公司名称,应收金额,应付金额,期初,单号,说明)  Values ( '2010-08-09 00:00:00.000','G001','大龙',1000.00,-1000.00,NULL,'JH-2010-08-09-001','付款单')
Insert Test2 (日期,公司编号,公司名称,应收金额,应付金额,期初,单号,说明)  Values ( NULL,'G001','大龙',-1500.00,1500.00,'期初应付',NULL,'期初应付')
Insert Test2 (日期,公司编号,公司名称,应收金额,应付金额,期初,单号,说明)  Values ( NULL,'001','讯达',20000.00,-20000.00,'期初应收',NULL,'期初应收')
Insert Test2 (日期,公司编号,公司名称,应收金额,应付金额,期初,单号,说明)  Values ( '2010-08-09 00:00:00.000','001','讯达',-10000.00,10000.00,'期初应收',NULL,'收款单')
Insert Test2 (日期,公司编号,公司名称,应收金额,应付金额,期初,单号,说明)  Values ( '2010-08-09 00:00:00.000','G001','大龙',1000.00,-1000.00,'期初应付',NULL,'付款单')
GO
Insert Test3 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,应收金额,应付金额,说明)  Values ( '2010-08-09 00:00:00.000','JH-2010-08-09-001','G001','大龙','GODEX 1100 PLUS','台',10.0000,1600.00,-16000.00,16000.00,'进货单')
Insert Test3 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,应收金额,应付金额,说明)  Values ( '2010-08-09 00:00:00.000','JH-2010-08-09-001','G001','大龙','GODEX 2100 PLUS','台',10.0000,3700.00,-37000.00,37000.00,'进货单')
Insert Test3 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,应收金额,应付金额,说明)  Values ( '2010-08-09 00:00:00.000','JHTH-2010-08-09-001','G001','大龙','GODEX 2100 PLUS','台',5.0000,3700.00,18500.00,18500.00,'进货退货单')
Insert Test3 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,应收金额,应付金额,说明)  Values ( '2010-08-09 00:00:00.000','XL-2010-08-09-001','001','讯达','GODEX 1100 PLUS','台',8.0000,2000.00,16000.00,-16000.00,'销售单')
Insert Test3 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,应收金额,应付金额,说明)  Values ( '2010-08-09 00:00:00.000','XL-2010-08-09-002','001','讯达','GODEX 1100 PLUS','台',3.0000,2000.00,6000.00,-6000.00,'销售单')
Insert Test3 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,应收金额,应付金额,说明)  Values ( '2010-08-09 00:00:00.000','XL-2010-08-09-003','001','讯达','GODEX 1100 PLUS','台',1.0000,2000.00,2000.00,-2000.00,'销售单')
Insert Test3 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,应收金额,应付金额,说明)  Values ( '2010-08-09 00:00:00.000','XL-2010-08-09-003','001','讯达','GODEX 2100 PLUS','台',1.0000,4500.00,4500.00,-4500.00,'销售单')
Insert Test3 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,应收金额,应付金额,说明)  Values ( '2010-08-09 00:00:00.000','XLTH-2010-08-09-001','001','讯达','GODEX 1100 PLUS','台',3.0000,2000.00,-6000.00,6000.00,'销售退货单')
Insert Test3 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,应收金额,应付金额,说明)  Values ( '2010-08-09 00:00:00.000','JH-2010-08-09-001  ','G001','大龙',NULL,NULL,NULL,NULL,1000.00,-1000.00,'付款单')
Insert Test3 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,应收金额,应付金额,说明)  Values ( NULL,NULL,'G001','大龙',NULL,NULL,NULL,NULL,-1500.00,1500.00,'期初应付')
Insert Test3 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,应收金额,应付金额,说明)  Values ( NULL,NULL,'001','讯达',NULL,NULL,NULL,NULL,20000.00,-20000.00,'期初应收')
Insert Test3 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,应收金额,应付金额,说明)  Values ( '2010-08-09 00:00:00.000',NULL,'001','讯达',NULL,NULL,NULL,NULL,-10000.00,10000.00,'收款单')
Insert Test3 (日期,单号,公司编号,公司名称,产品全名,单位,数量,单价,应收金额,应付金额,说明)  Values ( '2010-08-09 00:00:00.000',NULL,'001','大龙',NULL,NULL,NULL,NULL,1000.00,-1000.00,'付款单')
GO
select * from Test1
select * from Test2
---已知Test1和Test2如何通过查询句得到Test3 ?
select * from Test3 ---要得到的结果

3 个解决方案

#1

select isnull(a.日期,b.日期)日期 ,
isnull(a.单号,b.单号)单号 ,
isnull(a.公司编号,b.公司编号)公司编号 ,
isnull(a.公司名称,b.公司名称)公司名称 ,
 a.产品全名 ,
 a.单位  ,
 a.数量   ,
 a.单价   ,
isnull(case when a.说明 in ('销售单','付款单','进货退货单') then a.金额 else -a.金额 end,b.应收金额)应收金额 ,
isnull(case when a.说明 in ('销售单','付款单','进货退货单') then -a.金额 else a.金额 end,b.应付金额)应付金额 ,
isnull(a.说明,b.说明)说明 
from test1 a full join test2 b on a.[单号]=b.[单号] and a.公司编号=b.公司编号 and a.说明=b.说明

#2

select isnull(a.日期,b.日期)日期 ,isnull(a.单号,b.单号)单号 ,isnull(a.公司编号,b.公司编号)公司编号 ,
isnull(a.公司名称,b.公司名称)公司名称 , a.产品全名 , a.单位  , a.数量   , a.单价   ,
isnull(case when a.说明 in ('销售单','付款单','进货退货单') then a.金额 else -a.金额 end,b.应收金额)应收金额 ,
isnull(case when a.说明 in ('销售单','付款单','进货退货单') then -a.金额 else a.金额 end,b.应付金额)应付金额 ,
isnull(a.说明,b.说明)说明 
from test1 a right join test2 b on a.单号=b.单号 and a.公司名称=b.公司名称 and a.说明=b.说明
order by 3,2