我有一個資料庫如下    Table : Trans
Item Loc DocDate  Qty
---- --- -------- ---
001  L01 20040101  50
002  L02 20040105  40
001  L01 20040201  30
002  L02 20040204  20    從這資料表, 我可以抽出這樣的 Report
Item 全年 Qty 一月 Qty
---- -------- --------
001        80       50
002        60       40    Loc 全年 Qty 一月 Qty
--- -------- --------
L01       80       50
L02       60       40    但當我下 SQL 如下 :    Select 
  t.Item, Sum(t.Qty) As AccQty, Sum(t2.Qty) As CurQty
From Trans t
Left Outer Join Trans t2 on t.Item = t2.Item and 
  substring(t2.DocDate, 1, 6) = '200401'
Where substring(t.DocDate, 1, 4) = '2004'
Group By t.Item
Order By t.Item    但得出來的結果是 :
Item AccQty CurQty
---- ------ ------
001      80    100
002      60     80    CurQty 的數目不對了, 變了一倍,
請問各位知道原因所在嗎?
和有沒有解決方法靠一個 SQL Statement 去抽出這樣的結果呢?    請幫忙!    

Hi Lonnie,    Try this one, hope helpful to you !!

SELECT  T.ITEM,
        SUM(T.QTY) AS ACCQTY,
        T2.QTY AS CURQTY
FROM   (SELECT ITEM,SUM(QTY) AS QTY FROM TABLE1 WHERE SUBSTRING(DOCDATE,1,6) = '200401' GROUP BY ITEM) T2,
        TABLE1 T
WHERE   T2.ITEM = T.ITEM
AND     SUBSTRING(T.DOCDATE, 1, 4) = '2004'
GROUP BY
        T.ITEM,
        T2.QTY
ORDER BY
        T.ITEM

---------------------------------- 小弟才疏學淺，若有謬誤尚請不吝指教 ----------------------------------

------
Fishman

可行了, 謝謝你, 及教曉我用 Sub-query ! 真的多謝你!!!    

引言: Hi Lonnie, Try this one, hope helpful to you !!

SELECT  T.ITEM,
        SUM(T.QTY) AS ACCQTY,
        T2.QTY AS CURQTY
FROM   (SELECT ITEM,SUM(QTY) AS QTY FROM TABLE1 WHERE SUBSTRING(DOCDATE,1,6) = '200401' GROUP BY ITEM) T2,
        TABLE1 T
WHERE   T2.ITEM = T.ITEM
AND     SUBSTRING(T.DOCDATE, 1, 4) = '2004'
GROUP BY
        T.ITEM,
        T2.QTY
ORDER BY
        T.ITEM

---------------------------------- 小弟才疏學淺，若有謬誤尚請不吝指教 ---------------------------------- < face="Verdana, Arial, Helvetica">