请问，这种数据如何合并？

Question

本帖最后由 xjindf 于 2010-09-03 17:38:29 编辑

Answer 1

--> 测试数据: #TA
if object_id('tempdb.dbo.#TA') is not null drop table #TA
GO
create table #TA (编码 varchar(2),名称 varchar(1),价格 numeric(3,1),数量 int,总计 int)
insert into #TA
select '01','a',10.0,2,20 union all
select '02','b',15,1,15
--> 测试数据: #TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
GO 
create table #TB (编码 varchar(2),名称 varchar(1),价格 numeric(3,1),数量 int,总计 int)
insert into #TB
select '01','a',25.0,5,125 union all
select '03','c',40,1,40select 编码=ISNULL(A.编码,B.编码),
   名称=ISNULL(A.名称,B.名称),
  A价格=ISNULL(A.价格,0),
      A数量=ISNULL(A.数量,0),
  A总计=ISNULL(A.价格,0)*ISNULL(A.数量,0),
  B价格=ISNULL(B.价格,0),
  B数量=ISNULL(B.数量,0), 
  B总计=ISNULL(B.价格,0)*ISNULL(B.数量,0),
 价格差=ISNULL(B.价格,0)-ISNULL(A.价格,0)
from #TA A
FULL JOIN #TB B
ON A.编码=B.编码
  编码   名称   A价格                                     A数量         A总计                                     B价格                                     B数量         B总计                                     价格差
---- ---- --------------------------------------- ----------- --------------------------------------- --------------------------------------- ----------- --------------------------------------- ---------------------------------------
01   a    10.0                                    2           20.0                                    25.0                                    5           125.0                                   15.0
02   b    15.0                                    1           15.0                                    0.0                                     0           0.0                                     -15.0
03   c    0.0                                     0           0.0                                     40.0                                    1           40.0                                    40.0(3 行受影响)

Answer 2

价格差
如果是取绝对值，就这样  价格差=ABS(ISNULL(B.价格,0)-ISNULL(A.价格,0))