SELECT AcCID.*, AcCIM.Card_ID As Customer_Code, AcCard.Name As Customer_Name,AcCard.Address1 As Customer_Address1, AcCard.Address2 As Customer_Address2, AcCard.Address3 As Customer_Address3, AcCard.Tel1 As Customer_Phone1, AcCard.Tel2 As Customer_Phone2, AcCard.Fax As Customer_Fax, AcCard.Contact1 As Customer_Contact1, AcCard.Contact2 As Customer_Contact2,AcCIM.Ship_Address1, AcCIM.Ship_Tel1, AcCIM.Ship_Tel2, AcStk.ISBN, AcStk.Description1, AcStk.Barcode , (AcCID.Discount AcCID.Discount1) As ItemTotalDiscount, ((AcCID.Qty * AcCID.Price)-(AcCID.Discount AcCID.Discount1)) As ItemAmount,AcCIM.Discount_Account, (AcCIM.Amount - AcCIM.Discount AcCIM.Tax) As INVNetAmount, AcStkct.Description As Counter_Description From AcCID LEFT OUTER JOIN AcCIM ON AcCID.Document_No = AcCIM.Document_No LEFT OUTER JOIN AcStk ON AcCID.Stock_ID = AcStk.Code LEFT OUTER JOIN AcStkct ON AcCIM.Counter = AcStkct.Code LEFT OUTER JOIN AcCard ON AcCIM.Card_ID = AcCard.Code 當資料很多時要用很久拿資料,請問如果不用LEFT OUTER JOIN 有什么方法可以拿到一樣的資料又能減少時間? 我換成 SELECT AcCID.*, AcCIM.Card_ID As Customer_Code, AcCard.Name As Customer_Name,AcCard.Address1 As Customer_Address1, AcCard.Address2 As Customer_Address2, AcCard.Address3 As Customer_Address3, AcCard.Tel1 As Customer_Phone1, AcCard.Tel2 As Customer_Phone2, AcCard.Fax As Customer_Fax, AcCard.Contact1 As Customer_Contact1, AcCard.Contact2 As Customer_Contact2, AcCIM.Ship_Address1, AcCIM.Ship_Tel1, AcCIM.Ship_Tel2, AcStk.ISBN, AcStk.Description1, AcStk.Barcode , (AcCID.Discount AcCID.Discount1) As ItemTotalDiscount, ((AcCID.Qty * AcCID.Price)-(AcCID.Discount AcCID.Discount1)) As ItemAmount,AcCIM.Discount_Account, (AcCIM.Amount - AcCIM.Discount AcCIM.Tax) As INVNetAmount, AcStkct.Description As Counter_Description From AcCID, AcCIM , AcStk, AcStkct , AcCard Where AcCID.Document_No = AcCIM.Document_No and AcCID.Stock_ID = AcStk.Code and AcCIM.Counter = AcStkct.Code and AcCIM.Card_ID = AcCard.Code 不能拿到準確的資料,因為有些如(AcCID.Stock_ID)是空的就拿不出來了!又或者拿到重複的資料.

Hi chyap99: 小弟之前也處理過很多類似問題.. 資料量若真的很多, SQL 速度一定會受到影響. 這是肯定的.  不過還是有解, 有幾點方向給您建議 1. 儘可能使用Key 來做JOIN, 所以若有一組 Index 欄位包含Document_No ,Stock_ID,Counter,Card_ID , 對於SQL 執行效率絕對有幫助    2. 若資料量太大,對於即時資料不甚要求.建議做Summary Table.    將您需要的資料用Procedure run 完後放再另一個Table..    直接Select 這個新Table 保證速度下嚇叫...     <>~悠遊法國號~

2. 若資料量太大,對於即時資料不甚要求.建議做Summary Table. 將您需要的資料用Procedure run 完後放再另一個Table.. 直接Select 這個新Table 保證速度下嚇叫... 謝謝大大回覆. 請問大大有什麼Sample可以讓我參考怎樣做Summary Table 和 Procedure run嗎,我常要link好几個資料量大的table.

引言: 2. 若資料量太大,對於即時資料不甚要求.建議做Summary Table. 將您需要的資料用Procedure run 完後放再另一個Table.. 直接Select 這個新Table 保證速度下嚇叫... 謝謝大大回覆. 請問大大有什麼Sample可以讓我參考怎樣做Summary Table 和 Procedure run嗎,我常要link好几個資料量大的table.

Hi chyap99您好: 您可以將您的SQL執行後塞入另一個Table 1. 寫Procedure, 不過不同資料庫語法不同..不知道您用什麼資料庫..可能需要您Study一下 2. 寫 Delphi summary program..比較容易. 以2為例,您可以在FROM1 on create 事件中寫入 procedure TForm1.FormCreate(Sender: TObject); Var str: String; begin str:='insert into Summary_table select * from yourComplexSQL'; QUERY1.Close; QUERY1.SQL.TEXT:=str; QUERY1.EXECSQL; Close; end; 然後定時跑這隻程式.最簡單的方式放在"排定的工作", 例如每5分鐘跑一次. 此時您只要在您的程式中,開啟Summary table 即可 Select * from Summary_table 希望對您有幫助 ~悠遊法國號~

謝謝大大回覆! 其實我的SQL是要讓user自己選擇data的,所以還會有where doc_no between ... and card_id between ... etc, 那么我就不能在一早把data放進summary table 對嗎?因為我也不知要選什麼data,如都選完就沒差了吧?不知我有沒誤解大大的解說呢?