Select P.*,Isnull(sum(D.SaleInvDetQty),0) Qty From (Select * From (Select Distinct ProductCode From Product) P, (Select MM=1 Union All Select 2 Union All Select 3 Union All Select 4 Union All Select 5 Union All Select 6 Union All Select 7 Union All Select 8 Union All Select 9 Union All Select 10 Union All Select 11 Union All Select 12) M ) P left Join SaleInvDet D On P.ProductCode=D.ProductCode and Month(D.SaleInvDetTime)=P.MM and D.SaleInvDetTime between '2004/01/01' and '2004/12/30' Group By P.ProductCode,P.MM Order By P.ProductCode,P.MM 產生一個臨時表, SQL大概如上
From (Select * From (Select Distinct ProductCode From Product) P,
(Select MM=1 Union All Select 2
Union All Select 3 Union All Select 4
Union All Select 5 Union All Select 6
Union All Select 7 Union All Select 8
Union All Select 9 Union All Select 10
Union All Select 11 Union All Select 12) M
) P left Join SaleInvDet D
On P.ProductCode=D.ProductCode and Month(D.SaleInvDetTime)=P.MM
and D.SaleInvDetTime between '2004/01/01' and '2004/12/30'
Group By P.ProductCode,P.MM
Order By P.ProductCode,P.MM 產生一個臨時表, SQL大概如上