SQL 的問題 |
尚未結案
|
chyap99
一般會員 ![]() ![]() 發表:20 回覆:51 積分:24 註冊:2004-03-31 發送簡訊給我 |
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)是空的就拿不出來了!又或者拿到重複的資料.
|
yachanga
資深會員 ![]() ![]() ![]() ![]() ![]() 發表:24 回覆:335 積分:296 註冊:2003-09-27 發送簡訊給我 |
Hi chyap99:
小弟之前也處理過很多類似問題..
資料量若真的很多, SQL 速度一定會受到影響. 這是肯定的.
不過還是有解, 有幾點方向給您建議
1. 儘可能使用Key 來做JOIN, 所以若有一組 Index 欄位包含Document_No ,Stock_ID,Counter,Card_ID , 對於SQL 執行效率絕對有幫助 2. 若資料量太大,對於即時資料不甚要求.建議做Summary Table.
將您需要的資料用Procedure run 完後放再另一個Table..
直接Select 這個新Table 保證速度下嚇叫...
|
chyap99
一般會員 ![]() ![]() 發表:20 回覆:51 積分:24 註冊:2004-03-31 發送簡訊給我 |
|
yachanga
資深會員 ![]() ![]() ![]() ![]() ![]() 發表:24 回覆:335 積分:296 註冊:2003-09-27 發送簡訊給我 |
引言: 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 希望對您有幫助 ~悠遊法國號~ |
chyap99
一般會員 ![]() ![]() 發表:20 回覆:51 積分:24 註冊:2004-03-31 發送簡訊給我 |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |