create table ta(產品 char(10),單價 int,數量 int,總價 int)
create table tb(產品 char(10),單價 int,數量 int,總價 int)
insert into ta select '牙膏','5','10','50' union all select '搽','10','10','100'
union all select '搽2','20','10','200'
insert into tb select '牙膏','6','5','30' union all select '牙膏','7','4','28'
union all select '搽2','25','10','250'
select * from ta
select * from tb
select a.產品,a.單價,(a.數量 - isnull(c.數量,0)) as 數量,(a.數量 - isnull(c.數量,0))*單價 as 總價 ,isnull((c.總價+(a.數量 - isnull(c.數量,0))*a.單價 - a.總價),0) as 盈餘
from ta a left join (select 產品,sum(數量)as 數量,sum(總價) as 總價 from tb group by 產品) c
on a.產品 = c.產品