ADOQuery1.SQL.Clear; // 第1 ADOQuery1.SQL.Add('Select Sum(DLMoney) as DebitMoney from detailledge where DLDate >= ''' BD ''' and DLDate = ''' ED ''' and DlAccno = ''' Acc ''' and DL_Type = ''' '1' ''' '); ADOQuery11.SQL.Clear; // 第2 ADOQuery11.SQL.Add('Select Sum(DLMoney) as LenderMoney from detailledge where DLDate >= ''' BD ''' and DLDate = ''' ED ''' and DlAccno = ''' Acc ''' and DL_Type = ''' '2' ''' '); 可簡化一行SQL 嗎?

依您的需求，應是想將二段SQL組成一段處理。個人建議，有錯請指正。 SELECT (SELECT Sum(DLMoney) FROM detailledge WHERE DlAccno= A.DlAccno AND DL_Type = ''1'' ) DebitMoney, (SELECT Sum(DLMoney) FROM detailledge WHERE DlAccno= A.DlAccno AND DL_Type = ''2'' ) LenderMoney , FROM detailledge A where DLDate >= ''' BD ''' and DLDate = ''' ED ''' and DlAccno = ''' Acc '''' ========================= 讀萬卷書~不如來K.TOP走一遭 =========================

例如 : ADOQuery1.SQL.Add('Select DL_Type, Sum(DLMoney) as DebitMoney from detailledge where DLDate >= ''' BD ''' and DLDate = ''' ED ''' and DlAccno = ''' Acc ''' and (DL_Type = ''1'' or DL_Type = ''2'' '); ADOQuery1.SQL.Add(' group by DL_Type ') ; 大致方向是這樣 我沒測試過 你在試試看 只要針對DL_Type做group 就可以了 天行健 君子當自強不息~~@.@ 發表人 - T.J.B 於 2004/09/01 18:25:15 發表人 - T.J.B 於 2004/09/01 18:25:58 發表人 - T.J.B 於 2004/09/01 18:28:51

------
天行健
君子當自強不息~~@.@

[quote] 例如 : ADOQuery1.SQL.Add('Select DL_Type, Sum(DLMoney) as DebitMoney from detailledge where DLDate >= ''' BD ''' and DLDate = ''' ED ''' and DlAccno = ''' Acc ''' and (DL_Type = ''1'' or DL_Type = ''2'' '); ADOQuery1.SQL.Add(' group by DL_Type ') ; 大致方向是這樣 我沒測試過 你在試試看 只要針對DL_Type做group 就可以了 嗨版主您好: Sum(DLMoney) as DebitMoney 取dl_type='1' 好像少了Sum(DLMoney) as LenderMoney 取dl_type='2' 謝謝版主

謝謝likush, 修正一下可行,不過用此語法好像慢一些 用P4 2.4G PC(Access Database) ADOQuery1筆數13,197 ADOQuery2筆數462筆 ----------------------- 跑要花費 1分32秒 ADOQuery2.First; while not ADOQuery2.Eof do begin Acc:=InCome_ADOQuery2.FieldByName('heading').AsString; 一行sql語法 ADOQuery1.SQL.Add('Select (Select Sum(DLMoney) from detailledge where DlAccno = ''' Acc ''' and DL_Type = ''' '1' ''' ) as DebitMoney ,'); ADOQuery1.SQL.Add(' (Select Sum(DLMoney) from detailledge where DlAccno = ''' Acc ''' and DL_Type = ''' '2' ''' ) as LenderMoney '); ADOQuery1.SQL.Add(' From detailledge where DLDate >= ''' BD ''' and DLDate <= ''' ED ''' '); ADOQuery2.Next; ProgressBar1.StepIt; end; 如用2個ADOQuery run -------------------------- 花費時間1分鐘好像快些 ADOQuery1.SQL.Clear; // 第1 ADOQuery1.SQL.Add('Select Sum(DLMoney) as DebitMoney from detailledge where DLDate >= ''' BD ''' and DLDate = ''' ED ''' and DlAccno = ''' Acc ''' and DL_Type = ''' '1' ''' '); ADOQuery11.SQL.Clear; // 第2 ADOQuery11.SQL.Add('Select Sum(DLMoney) as LenderMoney from detailledge where DLDate >= ''' BD ''' and DLDate = ''' ED ''' and DlAccno = ''' Acc ''' and DL_Type = ''' '2' ''' '); 可有快一點嗎?謝謝

不好意思，提供了不好的方法。 ^^" 若以ACCESS來說, 您亦可參考利用IF 如IIF(DL_Type = ''1'', 1,0)的方式 可出現你要的結果,但可能針對資料部份您仍需稍作處理，不知您這邊的需求是在於將二段SQL簡化即可，或針對效率方面，您亦可參考用THREAD的方式。 ========================= 讀萬卷書~不如來K.TOP走一遭 =========================

引言: [quote] 例如 : ADOQuery1.SQL.Add('Select DL_Type, Sum(DLMoney) as DebitMoney from detailledge where DLDate >= ''' BD ''' and DLDate = ''' ED ''' and DlAccno = ''' Acc ''' and (DL_Type = ''1'' or DL_Type = ''2'' )');<---就是這個阿 ADOQuery1.SQL.Add(' group by DL_Type ') ; 大致方向是這樣 我沒測試過 你在試試看 只要針對DL_Type做group 就可以了 嗨版主您好: Sum(DLMoney) as DebitMoney 取dl_type='1' 好像少了Sum(DLMoney) as LenderMoney 取dl_type='2' 謝謝版主

你取出的sum(DLMoney) 所代表的都是同一個欄位吧 只是別名不一樣 天行健 君子當自強不息~~@.@ 發表人 - t.j.b 於 2004/09/01 23:39:07

select ,SUM(CASE WHEN DL_Type = 1 THEN DLMoney ELSE 0 END) as sum_1 ,SUM(CASE WHEN DL_Type = 2 THEN DLMoney ELSE 0 END) as sum_2 from xx ==取之於斯,用之於斯==

------
==取之於斯,用之於斯==

版主您好 ADOQuery1.SQL.Add('Select DL_Type, Sum(DLMoney) as DebitMoney from detailledge where DLDate >= ''' BD ''' and DLDate = ''' ED ''' and DlAccno = ''' Acc ''' and (DL_Type = ''1'' or DL_Type = ''2'' '); ADOQuery1.SQL.Add(' group by DL_Type ') ; 此語法RUN會有Error 我想到一個方法 select * into test ..... 暫存,然後再去Sum(....時間好像比較快些) 我的想法不知是否對,否則Sum時間蠻耗時 另外我去跑ms-sql看看會比access慢嗎?在告訴各位結果 謝謝大家

引言: 版主您好 ADOQuery1.SQL.Add('Select DL_Type, Sum(DLMoney) as DebitMoney from detailledge where DLDate >= ''' BD ''' and DLDate = ''' ED ''' and DlAccno = ''' Acc ''' and (DL_Type = ''1'' or DL_Type = ''2'') ');<---要多一個括號 ADOQuery1.SQL.Add(' group by DL_Type ') ; 此語法RUN會有Error 我想到一個方法 select * into test ..... 暫存,然後再去Sum(....時間好像比較快些) 我的想法不知是否對,否則Sum時間蠻耗時 也是可以啦 另外我去跑ms-sql看看會比access慢嗎?在告訴各位結果 謝謝大家

天行健 君子當自強不息~~@.@

[A]Access 資料庫使用SQL語法Select sum(a),sum(b) ....where ... 比較費時(筆數4000 50 秒) [B] 如先取 select * into tmpa from ... where dldate >= bd and dldate <=ed 再用do while 去run while not adoquery2.eof do begin x:=x a; z:=z b adoquery1.next end; // 用此法Acess超快 (15秒) 使用sql-2000 [A]方法比較快(耗15秒),反而[B]比較慢一些(耗28秒) 以上為小弟用碼錶測出報告,提供大家參考 謝謝大家指導