因為SQL中,需要算到日期跟COUNT數量
一各SQL 似乎無法把整年度的數量分成每各單月去分(還是我技術太差)
以下是我SQL

執行會出現csTemp18 找不到 Fieldbyname.九月統計數量
應該是十月蓋過他了 但我不曉得該怎麼改~.~

[code delphi]
procedure TForm1.Button13Click(Sender: TObject);
begin
with csTemp18 do
begin
Close;
Params.Clear;
CommandText := 'SELECT LOCATION "零件",COUNT(LOCATION) "九月統計數量" '
'FROM G_SN_REPAIR_LOCATION '
'WHERE RECID IN (SELECT RECID FROM G_SN_REPAIR_LOCATION WHERE RECID BETWEEN ''2007090100000'' AND ''2007093099999'') '
'AND LOCATION NOT IN (''N/A'') '
'GROUP BY LOCATION '
'ORDER BY LOCATION ';
CommandText := 'SELECT LOCATION "零件",COUNT(LOCATION) "十月統計數量" '
'FROM G_SN_REPAIR_LOCATION '
'WHERE RECID IN (SELECT RECID FROM G_SN_REPAIR_LOCATION WHERE RECID BETWEEN ''2007100100000'' AND ''2007103199999'') '
'AND LOCATION NOT IN (''N/A'') '
'GROUP BY LOCATION '
'ORDER BY LOCATION ';
commandText := CommandText;
open;
while not eof do
begin
With StringGrid1 do
begin
Cells[0,RowCount-1] := Fieldbyname('零件').AsString;
Cells[1,RowCount-1] := Fieldbyname('九月統計數量').AsString;
Cells[2,RowCount-1] := Fieldbyname('十月統計數量').AsString;
RowCount := RowCount 1;
end;
Next;
end;
Close;
end;
end;

[/code]

------
Program : Delphi 7
DataBase : Oracle 9i
Client : ClientDataSet

編輯記錄

1.sql有高階應用與初級應用, 如果不清楚更高技術的應用, 那就用土法吧
2.何不改為兩個query 分別執行 commontext , 就不會有蓋來蓋去的問題
3.高階應用可以利用多層select 方法, 這關係到你資料庫結構的規劃, 不是在這裡一紙可說明的

1.你的問題 CommandText 被覆寫了
2.在來是,基礎統計問題


data View


Select LOCATION "零件" ,COUNT(LOCATION) "數量" , subString(RECID,1,6) "Month"
From G_SN_REPAIR_LOCATION
Where LOCATION NOT IN (''N/A'')
GROUP BY LOCATION ,SubString(RECID,1,6)

fill data in UI

.....

=============================================================================

1. procedure TForm1.Button13Click(Sender: TObject);
2. begin
3. with csTemp18 do
4. begin
5. Close;
6. Params.Clear;
7. CommandText := 'SELECT LOCATION "零件",COUNT(LOCATION) "九月統計數量" '
8. 'FROM G_SN_REPAIR_LOCATION '
9. 'WHERE RECID IN (SELECT RECID FROM G_SN_REPAIR_LOCATION WHERE RECID BETWEEN ''2007090100000'' AND ''2007093099999'') '
10. 'AND LOCATION NOT IN (''N/A'') '
11. 'GROUP BY LOCATION '
12. 'ORDER BY LOCATION ';
13. CommandText := 'SELECT LOCATION "零件",COUNT(LOCATION) "十月統計數量" '
14. 'FROM G_SN_REPAIR_LOCATION '
15. 'WHERE RECID IN (SELECT RECID FROM G_SN_REPAIR_LOCATION WHERE RECID BETWEEN ''2007100100000'' AND ''2007103199999'') '
16. 'AND LOCATION NOT IN (''N/A'') '
17. 'GROUP BY LOCATION '
18. 'ORDER BY LOCATION ';
19. commandText := CommandText;
20. open;
21. while not eof do
22. begin
23. With StringGrid1 do
24. begin
25. Cells[0,RowCount-1] := Fieldbyname('零件').AsString;
26. Cells[1,RowCount-1] := Fieldbyname('九月統計數量').AsString;
27. Cells[2,RowCount-1] := Fieldbyname('十月統計數量').AsString;
28. RowCount := RowCount 1;
29. end;
30. Next;
31. end;
32. Close;
33. end;
34. end;

有點懷疑，你會不會程式設計
還是說你都是抄書，拼湊的？不經讓人做相關猜憶

因為正常的程式設計師，會知道他寫該行程式的用意
(簡化一下你的程式，方便說明)
CommandText := A;

CommandText := B;

CommandText := CommandText ;

open;
.....
挖哩勒！這......是什麼跟什麼？該不會是寫好玩的吧！

你既然將 A 指定給 CommandText ，就應該執行之，意即下 open 的指令，以便取回結果，然後在設定 B，再取回結果
程式碼本來就是固定的東西，第一次見到這種討價還價，舉棋不定的寫法

CommandText := A; <---- 根本就是白寫的，寫來爽的就是囉！

不然我們會這樣做

CommandText := A;

CommandText := CommandText B;

Open

將 A B 組合後，取得結果

另外

這是 Delphi ，是不分大小寫的，雖然我們仍喜歡大小寫應用，以利區分，但 Delphi 仍是一視同仁的
所以這一行 ---> commandText := CommandText ;
也是寫爽的囉！

讓人覺得，你是超級門外漢

希望不是這樣

不過如真是新手，請到新手區去，不是矮化你，而是這樣，給你合適程度的回答，才會幫助最大

因為你需要的不只是 SQL 上的解答



===================引 用 lovemari 文 章===================
因為SQL中,需要算到日期跟COUNT數量
一各SQL 似乎無法把整年度的數量分成每各單月去分(還是我技術太差)
以下是我SQL

執行會出現csTemp18 找不到 Fieldbyname.九月統計數量
應該是十月蓋過他了 但我不曉得該怎麼改~.~

[code delphi]
procedure TForm1.Button13Click(Sender: TObject);
begin
with csTemp18 do
begin
Close;
Params.Clear;
CommandText := 'SELECT LOCATION "零件",COUNT(LOCATION) "九月統計數量" '
'FROM G_SN_REPAIR_LOCATION '
'WHERE RECID IN (SELECT RECID FROM G_SN_REPAIR_LOCATION WHERE RECID BETWEEN ''2007090100000'' AND ''2007093099999'') '
'AND LOCATION NOT IN (''N/A'') '
'GROUP BY LOCATION '
'ORDER BY LOCATION ';
CommandText := 'SELECT LOCATION "零件",COUNT(LOCATION) "十月統計數量" '
'FROM G_SN_REPAIR_LOCATION '
'WHERE RECID IN (SELECT RECID FROM G_SN_REPAIR_LOCATION WHERE RECID BETWEEN ''2007100100000'' AND ''2007103199999'') '
'AND LOCATION NOT IN (''N/A'') '
'GROUP BY LOCATION '
'ORDER BY LOCATION ';
commandText := CommandText;
open;
while not eof do
begin
With StringGrid1 do
begin
Cells[0,RowCount-1] := Fieldbyname('零件').AsString;
Cells[1,RowCount-1] := Fieldbyname('九月統計數量').AsString;
Cells[2,RowCount-1] := Fieldbyname('十月統計數量').AsString;
RowCount := RowCount 1;
end;
Next;
end;
Close;
end;
end;

[/code]

編輯記錄

首先 先謝謝各位大大的指點迷津

那樓上大大說得沒錯
我是真的是新手...
我懂一些SQL而已,但是應用在Delphi上的程式設計 我是真的是新手

公司安排我寫這些報表 我也是想破頭想不出來
後來安排工程師幫我上速成的課
很多地方我也是照寫 但我不知道為什麼
唯一會自己動手的就是關於SQL的下法..我會去做應變
那寫在DELPHI上...我就...不知道該怎樣讓他呈現...
我還有買[Delphi 7 程式設計徹底研究]這本書 可看不是很懂
不隻到該如何重頭學習@_@ 我是真的很想學

我會多去新手區看看人家怎麼做 再來發問 ....^^

我想在請教 我已經把兩各SQL 分成兩各 然後再相加 , OPEN
可是變成csTemp19 找不到九月份數量了


[code delphi]
procedure TForm1.Button13Click(Sender: TObject);
var
s1 : string;
begin
with csTemp18 do
begin
Close;
Params.Clear;
commandText := 'SELECT LOCATION "零件",subString(RECID,1,6) "九月統計數量" '
'FROM SAJET.G_SN_REPAIR_LOCATION '
'WHERE RECID IN (SELECT RECID FROM SAJET.G_SN_REPAIR_LOCATION WHERE RECID BETWEEN ''2007090100000'' AND ''2007093099999'') '
'AND LOCATION NOT IN (''N/A'') '
'GROUP BY LOCATION '
'ORDER BY LOCATION ';
with csTemp19 do
begin
s1 := 'SELECT LOCATION "零件",COUNT(LOCATION) "十月統計數量" '
'FROM SAJET.G_SN_REPAIR_LOCATION '
'WHERE RECID IN (SELECT RECID FROM SAJET.G_SN_REPAIR_LOCATION WHERE RECID BETWEEN ''2007100100000'' AND ''2007103199999'') '
'AND LOCATION NOT IN (''N/A'') '
'GROUP BY LOCATION '
'ORDER BY LOCATION ';
commandText := commandText s1;
open;
while not eof do
begin
With StringGrid1 do
begin
Cells[0,RowCount-1] := Fieldbyname('零件').AsString;
Cells[1,RowCount-1] := Fieldbyname('九月統計數量').AsString;
Cells[2,RowCount-1] := Fieldbyname('十月統計數量').AsString;
RowCount := RowCount 1;
end;
end;
Next;
end;
Close;
end;
end;

[/code]

------
Program : Delphi 7
DataBase : Oracle 9i
Client : ClientDataSet

要加油呦!

請看ORACLE範例
SELECT X.LOCATION 零件, X.CNT_M_09 九月統計數量, C.CNT_M_10 十月統計數量 FROM
(
SELECT A.LOCATION, B.CNT_M_09 FROM
( SELECT DISTINCT LOCATION FROM G_SN_REPAIR_LOCATION ) A,
(
SELECT LOCATION ,COUNT(LOCATION) CNT_M_09
FROM G_SN_REPAIR_LOCATION
WHERE RECID IN (SELECT RECID FROM G_SN_REPAIR_LOCATION WHERE RECID BETWEEN '2007090100000' AND '2007093099999')
AND LOCATION NOT IN ('N/A')
GROUP BY LOCATION
)B
WHERE A.LOCATION=B.LOCATION( )
)X,
(
SELECT LOCATION ,COUNT(LOCATION) CNT_M_10
FROM G_SN_REPAIR_LOCATION
WHERE RECID IN (SELECT RECID FROM G_SN_REPAIR_LOCATION WHERE RECID BETWEEN '2007100100000' AND '2007103199999')
AND LOCATION NOT IN ('N/A')
GROUP BY LOCATION
)C
WHERE X.LOCATION=C.LOCATION( )

------
What do we live for if not to make life less difficult for each other?

編輯記錄

新手不可恥, 這個觀念一定要維持下去, 被人罵又不會少一塊肉, 但看你的程式顯然你還是不清楚兩個QUERY的觀念, 給你一個重點, 把WITH .... DO 的語法全部拿掉, 請直接指定 TMP19..COMMAND...  TMP18.COMMAND... 等方式來做, 你就會立刻知道你的問題點出在那裡了

不要陷入 WITH XXX DO 的迷思, 雖然很好用, 但如果你無法掌握自己很清楚的思緒的話, 還是少用一點, 寧可程式碼多打一點, 其實這兩者在編譯後沒有太大的差別

編輯記錄

這是一個非常常見的"交叉報表". 如果你用Access Database, 有內建報表精靈可用.
否則建議你寫一個後端query先, 再用Delphi前端處理.
建議SQL寫法:
SELECT LOCATION AS "零件" ,
( SELECT COUNT(LOCATION)
FROM G_SN_REPAIR_LOCATION sub9
WHERE sub9.LOCATION = mainTable.LOCATION
AND RECID BETWEEN ''2007090100000'' AND ''2007093099999'') AS "九月統計數量" ,
( SELECT COUNT(LOCATION)
FROM G_SN_REPAIR_LOCATION sub9
WHERE sub9.LOCATION = mainTable.LOCATION
AND RECID BETWEEN ''2007100100000'' AND ''2007103199999'') AS AS "十月統計數量"
FROM
( SELECT LOCATION FROM G_SN_REPAIR_LOCATION
WHERE LOCATION <> ''N/A''
GROUP BY LOCATION
ORDER BY LOCATION ) mainTable

------
===波士頓夜未眠===
What a wonderful world!!
Jazz up the world with jazz!!
==================

When I am not programming...
you can find me here:
http://www.holy-war.de/EN/World3/bin/?advertiser=63190

感恩各位

最近比較忙些

那我在研究各位大大的指點了

有結果我會立刻結案

造成不便請見諒

------
Program : Delphi 7
DataBase : Oracle 9i
Client : ClientDataSet

提醒一下, 並不是一定要有答案才能結案, 有時候雖然沒有提供滿意或直接的答案, 但如果這篇討論中對你實際的幫助的話, 你可以就對你認為最有幫助的回文者給予結案, 這是一種禮貌, 也是一種尊重, 如果都沒有給缺席也沒有關係, 但儘快結案對給予你幫助的回文者可以放下這個心, 至於你是不是有結案, 願意不願意再分享出來, 這是你自由心證啦!

如果你用資料庫:ORACLE
關於交叉報表，最近的心得如下：您可以
使用 SELECT .. GROUP BY ROLLUP 搭配 暫存檔，
不必再用SELECT .. Left outer join。
^_^
===================引 用 yubad2000 文 章===================
這是一個非常常見的"交叉報表". 如果你用Access Database, 有內建報表精靈可用.
否則建議你寫一個後端query先, 再用Delphi前端處理.

------
What do we live for if not to make life less difficult for each other?