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如上,功能已经实现,但是公司里非不让使用派生表,该怎么改?
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如上,功能已经实现,但是公司里非不让使用派生表,该怎么改?
e.DeptCode AS DeptCode,
m.LocName,
e.ItemCode AS ItemCode,
s.LocName AS ItemName,
ISNULL (e.StockQty, 0) AS StockQty,
ISNULL (e.StockAmt, 0) AS StockAmt,
ISNULL (sum(c.LossQty),0) AS LossQty,
ISNULL (sum(c.LossAmt),0) AS LossAmt,
ISNULL (sum(c.RQty), 0) AS RQty,
ISNULL (sum(c.RAmt), 0) AS RAmt,
ISNULL (sum(c.RNetAmt), 0) AS RNetAmt,
ISNULL (e.StockQty, 0) + ISNULL(SUM(c.TackQty), 0) AS BeforeQty,
ISNULL (e.StockAmt, 0) + ISNULL(SUM(c.TackAmt), 0) AS BeforeAmt,
ISNULL (SUM(c.TackQty), 0) AS TackQty,
ISNULL (SUM(c.TackAmt), 0) AS TackAmt
FROM DeptmentInfo m, ItemInfo s LEFT OUTER JOIN StockCard c
ON s.CustomCode = c.CustomCode AND s.DeptCode = c.DeptCode AND s.ItemCode
= c.ItemCode AND c.DeptCode NOT IN ('31', '32', '33', '34', '91', '92')
AND c.SaleDay > '2011-10-20' AND c.SaleDay <= '2011-11-20' AND c.Specific
<> 'C', ElecAccount e
WHERE s.CustomCode = e.CustomCode AND s.DeptCode = e.DeptCode AND s.ItemCode
= e.ItemCode AND e.DeptCode NOT IN ('31', '32', '33', '34', '91', '92')
AND e.ReportDate = '2011-11-20' AND s.DeptCode = m.DeptCode
GROUP BY e.StoreCode, e.DeptCode, m.LocName, e.ItemCode, s.LocName,
ISNULL (e.StockQty, 0), ISNULL (e.StockAmt, 0)