select a.* from #A a left join #B b on a.DDWID=b.DDWID
具体不知道你想达到什么目的SELECT ISNULL (A.DDWID,b.ddwid ) as ddwid , isnull (a.jiashhj,0) as jiashhj, isnull (a.chengbje,0) as chengbje, isnull (a.maoli,0) as maoli, isnull (b.jiashhj,0) as bjiashhj, isnull (b.chengbje,0) as bchengbje, isnull (b.maoli,0) as bmaoli from #a full join #b on a.ddwid=b.ddwid
SELECT ISNULL (A.DDWID,b.ddwid ) as ddwid , isnull (a.jiashhj,0)+isnull (a.chengbje,0) as chengbje, isnull (a.maoli,0)+isnull (b.jiashhj,0) as maoli, isnull (a.chengbje,0)+isnull (b.chengbje,0) as chengbje from #a full join #b on a.ddwid=b.ddwid
上面写错了---SELECT ISNULL (A.DDWID,b.ddwid ) as ddwid , isnull (a.jiashhj,0)+isnull (b.jiashhj,0) as jiashhj, isnull (a.chengbje,0)+isnull (b.chengbje,0) as chengbje, isnull (a.maoli,0)+isnull (b.maoli,0) as maolifrom #a full join #b on a.ddwid=b.ddwid
SELECT ISNULL (A.DDWID,b.ddwid ) as ddwid , isnull (a.jiashhj,0)-isnull (b.jiashhj,0) as jiashhj, isnull (a.chengbje,0)-isnull (b.chengbje,0) as chengbje, isnull (a.maoli,0)-isnull (b.maoli,0) as maolifrom (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_ckmx group by ddwid) a full join (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_thmx group by ddwid) b on a.ddwid=b.ddwid
create table pf_ckmx (ddwid varchar (11),jiashhj decimal (12,2),chengbje decimal (12,2),maoli decimal (12,2)) create table pf_thmx (ddwid varchar (11),jiashhj decimal (12,2),chengbje decimal (12,2),maoli decimal (12,2))insert into pf_ckmx select 'DDW00000001',3768.20,3768.20,0.00 union select 'DDW00000003',1367.66,197653.66,167765.82 insert into pf_thmx select 'DDW00000001',1664.20,1664.20,.00 union select 'DDW00000003',1367.66,197653.66,167765.82 union select 'DDW00000004',10.00,1972.6434,1677.47 ----相减 SELECT ISNULL (A.DDWID,b.ddwid ) as ddwid , isnull (a.jiashhj,0)-isnull (b.jiashhj,0) as jiashhj, isnull (a.chengbje,0)-isnull (b.chengbje,0) as chengbje, isnull (a.maoli,0)-isnull (b.maoli,0) as maolifrom (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_ckmx group by ddwid) a full join (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_thmx group by ddwid) b on a.ddwid=b.ddwidddwid jiashhj chengbje maoli ----------- ------- --------- ----- DDW00000001 2104.00 2461.6800 .00 DDW00000003 .00 .0000 .00 DDW00000004 -10.00 -2307.9888 -1677.47----相加 SELECT ISNULL (A.DDWID,b.ddwid ) as ddwid , isnull (a.jiashhj,0)+isnull (b.jiashhj,0) as jiashhj, isnull (a.chengbje,0)+isnull (b.chengbje,0) as chengbje, isnull (a.maoli,0)+isnull (b.maoli,0) as maolifrom (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_ckmx group by ddwid) a full join (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_thmx group by ddwid) b on a.ddwid=b.ddwid ddwid jiashhj chengbje maoli ----------- ------- --------- ----- DDW00000001 5432.40 6355.9080 .00 DDW00000003 .00 .0000 .00 DDW00000004 10.00 2307.9888 1677.47
----相加 SELECT ISNULL (A.DDWID,b.ddwid ) as ddwid , isnull (a.jiashhj,0)+isnull (b.jiashhj,0) as jiashhj, isnull (a.chengbje,0)+isnull (b.chengbje,0) as chengbje, isnull (a.maoli,0)+isnull (b.maoli,0) as maolifrom (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_ckmx group by ddwid) a full join (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_thmx group by ddwid) b on a.ddwid=b.ddwid ddwid jiashhj chengbje maoli ----------- ------- --------- ----- DDW00000001 5432.40 6355.9080 .00 DDW00000003 2735.32 462509.5644 335531.64 DDW00000004 10.00 2307.9888 1677.47
on a.DDWID=b.DDWID
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0) as jiashhj,
isnull (a.chengbje,0) as chengbje,
isnull (a.maoli,0) as maoli,
isnull (b.jiashhj,0) as bjiashhj,
isnull (b.chengbje,0) as bchengbje,
isnull (b.maoli,0) as bmaoli
from #a full join
#b
on a.ddwid=b.ddwid
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0)+isnull (a.chengbje,0) as chengbje,
isnull (a.maoli,0)+isnull (b.jiashhj,0) as maoli,
isnull (a.chengbje,0)+isnull (b.chengbje,0) as chengbje
from #a full join
#b
on a.ddwid=b.ddwid
DDW00000001 3768.20 3768.2190 .00
DDW00000003 3431.45 2941.2396 418.98--------------#b 数据DDW00000001 1664.20 1664.2080 .00
DDW00000003 1367.66 197653.6692 167765.82
DDW00000004 10.00 1972.6434 1677.47
我查询出来的数据要求DDW00000001,DDW00000003的数据进行汇总,同时也要把DDW00000004的数据
也查询出来。谢谢了
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0)+isnull (b.jiashhj,0) as jiashhj,
isnull (a.chengbje,0)+isnull (b.chengbje,0) as chengbje,
isnull (a.maoli,0)+isnull (b.maoli,0) as maolifrom #a full join
#b
on a.ddwid=b.ddwid
一个是批发出库明细pf_ckmx
一个是批发退货明细pf_thmx
我想应该是相减吧
如果相加了
单位金额就不对了吧!!!
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0)-isnull (b.jiashhj,0) as jiashhj,
isnull (a.chengbje,0)-isnull (b.chengbje,0) as chengbje,
isnull (a.maoli,0)-isnull (b.maoli,0) as maolifrom (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_ckmx group by ddwid) a full join
(select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_thmx group by ddwid) b
on a.ddwid=b.ddwid
create table pf_thmx (ddwid varchar (11),jiashhj decimal (12,2),chengbje decimal (12,2),maoli decimal (12,2))insert into pf_ckmx
select 'DDW00000001',3768.20,3768.20,0.00 union
select 'DDW00000003',1367.66,197653.66,167765.82
insert into pf_thmx
select 'DDW00000001',1664.20,1664.20,.00 union
select 'DDW00000003',1367.66,197653.66,167765.82 union
select 'DDW00000004',10.00,1972.6434,1677.47 ----相减
SELECT
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0)-isnull (b.jiashhj,0) as jiashhj,
isnull (a.chengbje,0)-isnull (b.chengbje,0) as chengbje,
isnull (a.maoli,0)-isnull (b.maoli,0) as maolifrom (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_ckmx group by ddwid) a full join
(select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_thmx group by ddwid) b
on a.ddwid=b.ddwidddwid jiashhj chengbje maoli
----------- ------- --------- -----
DDW00000001 2104.00 2461.6800 .00
DDW00000003 .00 .0000 .00
DDW00000004 -10.00 -2307.9888 -1677.47----相加
SELECT
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0)+isnull (b.jiashhj,0) as jiashhj,
isnull (a.chengbje,0)+isnull (b.chengbje,0) as chengbje,
isnull (a.maoli,0)+isnull (b.maoli,0) as maolifrom (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_ckmx group by ddwid) a full join
(select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_thmx group by ddwid) b
on a.ddwid=b.ddwid
ddwid jiashhj chengbje maoli
----------- ------- --------- -----
DDW00000001 5432.40 6355.9080 .00
DDW00000003 .00 .0000 .00
DDW00000004 10.00 2307.9888 1677.47
SELECT
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0)+isnull (b.jiashhj,0) as jiashhj,
isnull (a.chengbje,0)+isnull (b.chengbje,0) as chengbje,
isnull (a.maoli,0)+isnull (b.maoli,0) as maolifrom (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_ckmx group by ddwid) a full join
(select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_thmx group by ddwid) b
on a.ddwid=b.ddwid
ddwid jiashhj chengbje maoli
----------- ------- --------- -----
DDW00000001 5432.40 6355.9080 .00
DDW00000003 2735.32 462509.5644 335531.64
DDW00000004 10.00 2307.9888 1677.47