工作中遇到的问题

编写查找款号，销售金额和当前库存的SQL语句时，要求对款号进行分组，试了几种方法都不行，下面做个总结

1.交叉查询，查出来的数据很大，总是不对
select tbT_Sale_dtl.warecode,sum(tbT_Sale_dtl.qty) sqty,sum(tbi_Inventory.qty) kucun,
Convert(char(10),SaleDate,120) ColItem
from uvs_ware,tbT_Sale_dtl ,tbT_Sale_hdr ,tbi_Inventory
where tbT_Sale_dtl.warecode=uvs_ware.warecode 
and tbT_Sale_dtl.doccode=tbT_Sale_hdr.doccode 
and tbi_Inventory.warecode=uvs_ware.warecode
group by tbT_Sale_dtl.warecode,Convert(char(10),SaleDate,120)
order by tbT_Sale_dtl.warecode

2.使用left join和union all进行查询，但在后台拼接查询条件是，查出来的数据是按照仓库名对款号进行分组了，造成我选择了10个仓库，一个款号就出现10次的结果，而我要的结果是用款号进行分组；还有的拼接条件时无法进行拼接
select u.warespec,saleQty,StockQty
from (
( select warecode ,sum(qty) saleQty from tbT_Sale_dtl as SD,tbt_sale_hdr as SH where SH.doccode = SD.docCode  group by warecode  ) A 
left join (
select warecode,stockcode, sum(i.qty) stockQty from tbI_Inventory I group by stockcode, warecode
) B on A.warecode=B.warecode ),
uvs_ware u
where A.warecode=u.warecode

---------------------

select warecode,sum(saleqty),sum(stockqty)
from (
select warecode ,sum(qty) saleQty,0 stockQty from tbT_Sale_dtl as SD,tbt_sale_hdr as SH where SH.doccode = SD.docCode group by warecode
union all
select warecode, 0,sum(i.qty) stockQty from tbI_Inventory I group by stockcode, warecode ) X
group by warecode 

3.最后用的是分别对销售和库存进行分组查询，再进行拼接查询条件，才查询正确
select A.warespec,A.qtys,B.qtys1
from
(select warespec,sum(qty) qtys from tbt_sale_hdr,tbt_sale_dtl,uvs_ware
where tbt_sale_hdr.doccode=tbt_sale_dtl.doccode 
and  tbt_sale_dtl.warecode= uvs_ware.warecode
group by warespec) as A,
(select warespec,sum(qty) qtys1  from tbi_inventory,uvs_ware,tbS_UnitDept
where tbi_inventory.warecode=uvs_ware.warecode 
and tbi_inventory.StockCode=tbS_UnitDept.unitcode and DeptType='2'
group by warespec) as B
where A.warespec=B.warespec
ORDER BY A.warespec

 此文是对工作时编写语句的一个记录