以下為我寫的SQL語法:
SELECT A.SupplierID, S.SupplierAttribName,
(SELECT SUM(X.PurchaseAmount - X.PurchaseReturnAmount)
FROM PurchaseAnalyst X
WHERE X.CompanyID = :CompanyID
AND X.SupplierID = A.SupplierID
AND X.PurchaseYear = A.PurchaseYear AND X.PurchaseMonth = '01') AS Amount01,
(SELECT SUM(X.PurchaseAmount - X.PurchaseReturnAmount)
FROM PurchaseAnalyst X
WHERE X.CompanyID = :CompanyID
AND X.SupplierID = A.SupplierID
AND X.PurchaseYear = A.PurchaseYear AND X.PurchaseMonth = '02') AS Amount02,
(SELECT SUM(X.PurchaseAmount - X.PurchaseReturnAmount)
FROM PurchaseAnalyst X
WHERE X.CompanyID = :CompanyID
AND X.SupplierID = A.SupplierID
AND X.PurchaseYear = A.PurchaseYear AND X.PurchaseMonth = '03') AS Amount03,
(SELECT SUM(X.PurchaseAmount - X.PurchaseReturnAmount)
FROM PurchaseAnalyst X
WHERE X.CompanyID = :CompanyID
AND X.SupplierID = A.SupplierID
AND X.PurchaseYear = A.PurchaseYear AND X.PurchaseMonth = '04') AS Amount04,
(SELECT SUM(X.PurchaseAmount - X.PurchaseReturnAmount)
FROM PurchaseAnalyst X
WHERE X.CompanyID = :CompanyID
AND X.SupplierID = A.SupplierID
AND X.PurchaseYear = A.PurchaseYear AND X.PurchaseMonth = '05') AS Amount05,
(SELECT SUM(X.PurchaseAmount - X.PurchaseReturnAmount)
FROM PurchaseAnalyst X
WHERE X.CompanyID = :CompanyID
AND X.SupplierID = A.SupplierID
AND X.PurchaseYear = A.PurchaseYear AND X.PurchaseMonth = '06') AS Amount06,
(SELECT SUM(X.PurchaseAmount - X.PurchaseReturnAmount)
FROM PurchaseAnalyst X
WHERE X.CompanyID = :CompanyID
AND X.SupplierID = A.SupplierID
AND X.PurchaseYear = A.PurchaseYear AND X.PurchaseMonth = '07') AS Amount07,
(SELECT SUM(X.PurchaseAmount - X.PurchaseReturnAmount)
FROM PurchaseAnalyst X
WHERE X.CompanyID = :CompanyID
AND X.SupplierID = A.SupplierID
AND X.PurchaseYear = A.PurchaseYear AND X.PurchaseMonth = '08') AS Amount08,
(SELECT SUM(X.PurchaseAmount - X.PurchaseReturnAmount)
FROM PurchaseAnalyst X
WHERE X.CompanyID = :CompanyID
AND X.SupplierID = A.SupplierID
AND X.PurchaseYear = A.PurchaseYear AND X.PurchaseMonth = '09') AS Amount09,
(SELECT SUM(X.PurchaseAmount - X.PurchaseReturnAmount)
FROM PurchaseAnalyst X
WHERE X.CompanyID = :CompanyID
AND X.SupplierID = A.SupplierID
AND X.PurchaseYear = A.PurchaseYear AND X.PurchaseMonth = '10') AS Amount10,
(SELECT SUM(X.PurchaseAmount - X.PurchaseReturnAmount)
FROM PurchaseAnalyst X
WHERE X.CompanyID = :CompanyID
AND X.SupplierID = A.SupplierID
AND X.PurchaseYear = A.PurchaseYear AND X.PurchaseMonth = '11') AS Amount11,
(SELECT SUM(X.PurchaseAmount - X.PurchaseReturnAmount)
FROM PurchaseAnalyst X
WHERE X.CompanyID = :CompanyID
AND X.SupplierID = A.SupplierID
AND X.PurchaseYear = A.PurchaseYear AND X.PurchaseMonth = '12') AS Amount12,
(SELECT SUM(X.PurchaseAmount - X.PurchaseReturnAmount)
FROM PurchaseAnalyst X
WHERE X.CompanyID = :CompanyID
AND X.SupplierID = A.SupplierID
AND X.PurchaseYear = A.PurchaseYear) AS Amount
FROM PurchaseAnalyst A, Supplier S
WHERE A.CompanyID = :CompanyID AND A.PurchaseYear = :PurchaseYear
AND A.SupplierID = S.SupplierID
AND A.SupplierID >= :BeginSupplierID AND A.SupplierID <= :EndSupplierID
ORDER BY A.SupplierID
----------------------------------------------------------------------------------------------------------
我想依據 SupplierID 來做群組分類，讓資料在各個月份加總後，只顯示一次，但是目前報表會依據SupplierID的筆數重複顯示！
這個問題讓我很困擾，麻煩高手幫忙解決，而且很急～
資料表的設計如下：
CompanyID PurchaseYear PurchaseMonth SupplierID ProductID PurchaseQuantity PurchaseAmount
PurchaseReturnQuantity PurchaseReturnAmount
此資料表為一個進貨分析檔！