日期:2014-05-18  浏览次数:20509 次

怎么样避免使用派生表???
SQL code
select A.ItemCode,C.LocName,A.DeptCode,D.LocName,
isnull(B.RQty,0) as RQty,isnull(B.RAmt,0) as RAmt,isnull(B.RNetAmt,0) as RNetAmt,
isnull(A.StockQty,0) as StockQty,isnull(A.StockAmt,0) as StockAmt,
isnull(B.LossQty,0) as LossQty,isnull(B.LossAmt,0) as LossAmt,
isnull(B.TackQty,0) as TackQty,isnull(B.TackAmt,0) as TackAmt,
(isnull(StockQty,0)+isnull(TackQty,0)) as BeforeQty,(isnull(StockAmt,0)+isnull(TackAmt,0)) as BeforeAmt   
from (
SELECT ItemCode,
   DeptCode,
   SUM(StockQty) AS StockQty,
   SUM(StockAmt) AS StockAmt 
   FROM ElecAccount 
   WHERE ReportDate = '2011-11-20'
   GROUP BY ItemCode, DeptCode
)A
 left join 
 (
SELECT ItemCode,
   SUM(CASE 
   WHEN Specific <> 'C' THEN RQty 
   END) AS RQty,
   SUM(CASE 
   WHEN Specific <> 'C' THEN RAmt 
   END) AS RAmt,
   SUM(CASE 
   WHEN Specific <> 'C' THEN RNetAmt 
   END) AS RNetAmt,
   SUM(LossQty) AS LossQty,
   SUM(LossAmt) AS LossAmt,
   SUM(TackQty) AS TackQty,
   SUM(TackAmt) AS TackAmt 
   FROM StockCard 
   WHERE SaleDay > '2011-10-20' AND SaleDay <= '2011-11-20'
   GROUP BY ItemCode
)B
on A.ItemCode = B.ItemCode, ItemInfo C ,DeptmentInfo D
where A.ItemCode = C.ItemCode and A.DeptCode = D.DeptCode

我的SQL如上,功能已经实现,但是公司里非不让使用派生表,该怎么改?

------解决方案--------------------
“不让使用派生表”的理由是什么?
------解决方案--------------------
晕 明明不好实现 还不让用?
------解决方案--------------------
那用什么,难道用表函数