select spbh,sum(spsl),max(spcd) from (select spbh,spsl,spcd from a union all select spbh,spsl,'0' as spcd from b) c group by spbh order by spbh
B表中有的商品编号而A表中没有的商品编号,就把B中的记录插入到A表中并把商品产地赋值为字符0
select t1.spbh,t1.spsl,isnull(t2.spcd,'0')as spcd from (select spbh,sum(spsl) as spsl from (select spbh,spsl from a union select spbh,spsl from b)t0 group by spbh)t1 left join a t2 on t1.spbh=t2.spbh order by t1.spbh
报错,syntaz error in from clause!
Select a.spbh,sum(a.spsl),Max(a.spcd) From (select spbh,spsl,spcd=convert(varchar(20),0) from tblB b Union select spbh,spsl,spcd from tblA a) a Group by a.spbh
SELECT ISNULL(A.Aspbh,A.Bspbh) AS spbh, ISNULL(A.Aspsl,0)+ISNULL(A.Bspsl,0) AS spsl, CASE WHEN A.spcd>'' THEN A.spcd ELSE '0' END AS spcd FROM (SELECT A.spbh AS Aspbh, B.spbh AS Bspbh, A.spsl AS Aspsl, B.spsl AS Aspsl, A.spcd FROM A表 A FULL JOIN B表 B ON A.spbh=B.spbh) A
(select spbh,spsl,spcd from a union all
select spbh,spsl,'0' as spcd from b) c
group by spbh
order by spbh
(select spbh,sum(spsl) as spsl from
(select spbh,spsl from a
union
select spbh,spsl from b)t0 group by spbh)t1 left join a t2 on t1.spbh=t2.spbh
order by t1.spbh
From (select spbh,spsl,spcd=convert(varchar(20),0) from tblB b Union select spbh,spsl,spcd from tblA a) a
Group by a.spbh
ISNULL(A.Aspsl,0)+ISNULL(A.Bspsl,0) AS spsl,
CASE WHEN A.spcd>'' THEN A.spcd ELSE '0' END AS spcd
FROM (SELECT A.spbh AS Aspbh, B.spbh AS Bspbh, A.spsl AS Aspsl, B.spsl AS Aspsl, A.spcd
FROM A表 A FULL JOIN B表 B ON A.spbh=B.spbh) A