sql 查询语句 - MS-SQL Server / 基础类

进货表：id(销货id)、huo_Id(货物id)、huo_JinHuoNum（进货数量）、huo_JinHuoDate(进货时间)
销货表：id(进货id)、huo_Id（货物id）、huo_SaleNum(销货数量)、huo_SaleDate(销货时间)
货物表：huo_Id（货物id）、huo_Name（货物名称）

要查询一段时间内每种货物的进货总数和销货总数
结果形式为：huo_Name、Total_JinHuoNum、Total_SaleNum

请问这个查询语句怎么写？

---

SQL code:
select
c.huo_Name,sum(huo_JinHuoNum) as Total_JinHuoNum,sum(huo_SaleNum) as Total_SaleNum
from
进货表 a,销货表 b,货物表 c
where
a.huo_Id=c.huo_Id and b.huo_Id=c.huo_Id
and
a.huo_JinHuoDate between 'xx' and 'xx'
and
b.huo_SaleDate between 'xx' and 'xx'
group by
c.huo_Name

---

try
SQL code:
select a.huo_Name,
sum(b.huo_JinHuoNum) as Total_JinHuoNum,
sum(b.huo_SaleNum) as Total_SaleNum
from 货物表 a
left join
(select huo_Id,huo_JinHuoNum,0 as huo_SaleNum from 进货表 where huo_JinHuoDate between xxx and xxx
union all
select huo_Id,0,huo_SaleNum from 销货表 where huo_SaleDate between xxx and xxx
) b
on a.huo_Id=b.huo_Id
group by a.huo_Name

---

SQL code:

select ht.name, sum(huo_JinHuoNum) as