想让两DATABASE 内的值左右显示并相加:两个DATABASE 名称分别为DS1,DS2select CODE,mount AS M1 from DS1.T1 LEFT JOIN DS1.T2
ONT1.CODE=T2.CODE
查询结果:
CODE M1
001 20
002 50
select CODE,mount AS M2 from DS2.T1 LEFT JOIN DS1.T2
ONT1.CODE=T2.CODE CODE M1
001 30
002 50
目的:想让两DATABASE 内的值这样显示:CODE M1 M2 M1+M20001 20 30 500002 50 50 100
ONT1.CODE=T2.CODE
查询结果:
CODE M1
001 20
002 50
select CODE,mount AS M2 from DS2.T1 LEFT JOIN DS1.T2
ONT1.CODE=T2.CODE CODE M1
001 30
002 50
目的:想让两DATABASE 内的值这样显示:CODE M1 M2 M1+M20001 20 30 500002 50 50 100
a.mount +b.mount as [M1+M2]
from DS1.T1 a LEFT JOIN DS1.T2 b
ON a.CODE=b.CODE
a.mount AS M1,
b.mount as M2,
isnull(a.mount,0) +isnull(b.mount,0) as [M1+M2]
from DS1.T1 a full JOIN DS1.T2 b
ON a.CODE=b.CODE
如果是在T1表上的,2楼的就没问题了
如果是存放在T2表上的,那么就应该这样
SELECT A.CODE AS CODE ,A.MOUNT AS M1 ,B.MOUNT AS M2,ISNULL(A.MOUNT,0)+ISNULL(B.MOUNT,0) AS 'M1+M2'
FROM (select CODE,mount AS M1 from DS1.T1 LEFT JOIN DS1.T2
ON T1.CODE=T2.CODE ) A
INNER JOIN
(select CODE,mount AS M2 from DS2.T1 LEFT JOIN DS1.T2
ON T1.CODE=T2.CODE) B ON A.CODE=B.CODE