第五節(jié) group by分組的應(yīng)用
首先先說說分組是怎樣工作的。舉個例子：
表：temp1有以下記錄：
bh sl
aaa 1
aaa 4
ccc 2
bbb 5
aaa 9
bbb 7
現(xiàn)在要統(tǒng)計一下temp1有中多少種編號，各種編號的總數(shù)量又是多少。很明顯，這是使用分組。
sele bh,sum(sl) as total_sl ;
from temp1 ;
grou by bh ;
orde by bh ;
into curs temp2
命令的運行過程就象投票選舉中的點票過程一樣。它中間到底是采用什么樣的技術(shù)來儲存中間結(jié)果我不知道，但不這重要，我現(xiàn)在假設(shè)它使用臨時表(實際上我想不大可能)，這樣對結(jié)果沒影響，但又容易理解。
1、是逐個掃描記錄。每遇到一個新的編號，就為這個編號建立一個臨時表(再次重申，使用臨時表只是我的假設(shè)布局，真正的處理方法我不知道)，然后把這個記錄的內(nèi)容放進(jìn)對應(yīng)的臨時表中去。如果不是新編號，就直接把記錄內(nèi)容放進(jìn)對應(yīng)的臨時表中去。
2、全部記錄掃描完了，各種編號對應(yīng)的臨時表也產(chǎn)生了，現(xiàn)在就是對每一個臨時表采用sum()進(jìn)行統(tǒng)計了。比如編號為"aaa"的臨時表是cursor1，那它的內(nèi)容就是：
Aaa 1
Aaa 4
Aaa 9
現(xiàn)在進(jìn)行sum()統(tǒng)計了，它的效果就相當(dāng)于sele bh,sum(sl) as total_sl from cursor1。每一個編號的臨時表都要進(jìn)行這樣的sum()統(tǒng)計，然后把每個編號的統(tǒng)計結(jié)果聯(lián)合起來，就想使用union一樣，最后得出的結(jié)果就是：
aaa 14
ccc 2
bbb 12
3、然后再給bh排序，結(jié)果就是：
aaa 14
bbb 12
ccc 2
看了上面那個例子，再看下面這條命令，想必也可以理解了吧：
sele cpk.cpbh,cpk.cpmc,sum(iif(isnull(thdmx.thsl),0,thdmx.thsl) as total_thsl ;
from cpk left join thdmx ;
on cpk.cpbh=thdmx.cpbh ;
grou by cpk.cpbh ;
orde by cpk.cpbh ;
into curs temp1
產(chǎn)品表左聯(lián)接提貨明細(xì)表，這樣沒有提過的產(chǎn)品，在聯(lián)接后的臨時表中，對應(yīng)的提貨數(shù)量就是個null值。到了分組，第一步完成后，生成的臨時表中也仍然有null值，所以在第二步進(jìn)行sum命令時，就要使用iif(isnull())來過濾了。過濾的結(jié)果就是把null值改為0，其它的不變，然后再由sum進(jìn)行匯總。最后第三步再排序，結(jié)果就出來了。
以上是分組的最基本用法?，F(xiàn)在說說分組的一些古怪用法。
一、使用recn()。例子一：
有種情況，一大段本來是應(yīng)該連續(xù)的號碼，但中間地漏掉了一些號碼，現(xiàn)在要把這些漏了的號碼找出來?？峙潞芏嗳?，一來就是使用scan endscan，再加上一些中間變量以作為判斷依據(jù)，最后寫成的代碼有一大段，運行起來慢得要死。但如果采用以下這種方法，我想即使它不是，但也是很好的了。數(shù)據(jù)如下：
1、6、3、5、2、7、9、10、15、16
在這些數(shù)據(jù)當(dāng)中，要找出漏掉了的4、8、11、12、13、14。
1、首先排序
sele bh from temp1 orde by bh into curs temp1
結(jié)果是：
1、2、3、5、6、7、9、10、15、16
如果源表中的物理記錄順序和號碼的順序一樣就不用做這一步了。
2、取temp1中各個記錄的recn()，即記錄號，然后把編號減去記錄號
sele bh,val(bh-recn()) as aa from temp1 into curs temp2
結(jié)果就是：
Bh aa
1 0
2 0
3 0
5 1
6 1
7 1
9 2
10 2
15 6
16 6
3、對aa使用分組，取每組中最小的bh和的bh
sele min(bh) as minbh,max(bh) as maxbh from temp2 grou by aa into curs temp3
結(jié)果如下：
minbh maxbh
1 3
5 7
9 10
15 16
4、現(xiàn)在結(jié)果很明顯了，某個記錄的minbh跟它上面那個記錄的maxbh中間相差的，就是漏掉的號碼?，F(xiàn)在才使用scan endscan就容易了。
以上方法適用于數(shù)據(jù)量大、第三步的結(jié)果比源表少很多記錄的情況下才會發(fā)揮效果。因為scan的速度不能和Select - SQL相比，何況它還要作條件判斷。雖然這種方法使用了二條全遍歷的SQL命令，但這是沒過濾條件的SQL命令，速度是很快的，只是分組要多點時間而已。同時只適用于編號沒有重復(fù)的情況。如果編號有重復(fù)，那在第一步的時候，就要使用：
sele bh from temp1 goru by bh orde by bh into curs temp1
把多余的編號去掉。如果想了解那些編號是重復(fù)的，就可以使用：
sele bh,coun(bh) as aa from temp1 grou by bh orde by bh havi aa>1 into curs temp1
結(jié)果就是重復(fù)的編號，對應(yīng)的aa字段就是重復(fù)的次數(shù)。
在第二步，好象可以不用二條命令就可以得出結(jié)果了，只是暫時還沒想到，不知各位對些有什么看法。
上面這種方法有個缺點：就是使用了sum()函數(shù)，而它是只能統(tǒng)計數(shù)值型的數(shù)據(jù)，其它字符型、日期型等是不能統(tǒng)計的。所以如果想把一個表中所有記錄的產(chǎn)品名稱都串起來，變成一個字符串，那是不能的。但對于日期型，變通一下，有時還是可以使用的。
二、日期相減的結(jié)果是數(shù)值，從而可以使用sum()函數(shù)。例子二
編號 名稱 入庫日期
1 網(wǎng)卡 2000.09.02
1 網(wǎng)卡 2000.09.03
1 網(wǎng)卡 2000.09.05
2 vfp 2000.09.05
2 vfp 2000.09.06
說明：把編號和名稱相同，入庫日期相間為1的記錄合并為一條，并加入另一個表里，如結(jié)果：
編號 名稱 入庫日期 （表2和表1結(jié)構(gòu)一樣）
1 網(wǎng)卡 2000.09.03
1 網(wǎng)卡 2000.09.05
2 vfp 2000.09.06
首先先說說分組是怎樣工作的。舉個例子：
表：temp1有以下記錄：
bh sl
aaa 1
aaa 4
ccc 2
bbb 5
aaa 9
bbb 7
現(xiàn)在要統(tǒng)計一下temp1有中多少種編號，各種編號的總數(shù)量又是多少。很明顯，這是使用分組。
sele bh,sum(sl) as total_sl ;
from temp1 ;
grou by bh ;
orde by bh ;
into curs temp2
命令的運行過程就象投票選舉中的點票過程一樣。它中間到底是采用什么樣的技術(shù)來儲存中間結(jié)果我不知道，但不這重要，我現(xiàn)在假設(shè)它使用臨時表(實際上我想不大可能)，這樣對結(jié)果沒影響，但又容易理解。
1、是逐個掃描記錄。每遇到一個新的編號，就為這個編號建立一個臨時表(再次重申，使用臨時表只是我的假設(shè)布局，真正的處理方法我不知道)，然后把這個記錄的內(nèi)容放進(jìn)對應(yīng)的臨時表中去。如果不是新編號，就直接把記錄內(nèi)容放進(jìn)對應(yīng)的臨時表中去。
2、全部記錄掃描完了，各種編號對應(yīng)的臨時表也產(chǎn)生了，現(xiàn)在就是對每一個臨時表采用sum()進(jìn)行統(tǒng)計了。比如編號為"aaa"的臨時表是cursor1，那它的內(nèi)容就是：
Aaa 1
Aaa 4
Aaa 9
現(xiàn)在進(jìn)行sum()統(tǒng)計了，它的效果就相當(dāng)于sele bh,sum(sl) as total_sl from cursor1。每一個編號的臨時表都要進(jìn)行這樣的sum()統(tǒng)計，然后把每個編號的統(tǒng)計結(jié)果聯(lián)合起來，就想使用union一樣，最后得出的結(jié)果就是：
aaa 14
ccc 2
bbb 12
3、然后再給bh排序，結(jié)果就是：
aaa 14
bbb 12
ccc 2
看了上面那個例子，再看下面這條命令，想必也可以理解了吧：
sele cpk.cpbh,cpk.cpmc,sum(iif(isnull(thdmx.thsl),0,thdmx.thsl) as total_thsl ;
from cpk left join thdmx ;
on cpk.cpbh=thdmx.cpbh ;
grou by cpk.cpbh ;
orde by cpk.cpbh ;
into curs temp1
產(chǎn)品表左聯(lián)接提貨明細(xì)表，這樣沒有提過的產(chǎn)品，在聯(lián)接后的臨時表中，對應(yīng)的提貨數(shù)量就是個null值。到了分組，第一步完成后，生成的臨時表中也仍然有null值，所以在第二步進(jìn)行sum命令時，就要使用iif(isnull())來過濾了。過濾的結(jié)果就是把null值改為0，其它的不變，然后再由sum進(jìn)行匯總。最后第三步再排序，結(jié)果就出來了。
以上是分組的最基本用法?，F(xiàn)在說說分組的一些古怪用法。
一、使用recn()。例子一：
有種情況，一大段本來是應(yīng)該連續(xù)的號碼，但中間地漏掉了一些號碼，現(xiàn)在要把這些漏了的號碼找出來?？峙潞芏嗳?，一來就是使用scan endscan，再加上一些中間變量以作為判斷依據(jù)，最后寫成的代碼有一大段，運行起來慢得要死。但如果采用以下這種方法，我想即使它不是，但也是很好的了。數(shù)據(jù)如下：
1、6、3、5、2、7、9、10、15、16
在這些數(shù)據(jù)當(dāng)中，要找出漏掉了的4、8、11、12、13、14。
1、首先排序
sele bh from temp1 orde by bh into curs temp1
結(jié)果是：
1、2、3、5、6、7、9、10、15、16
如果源表中的物理記錄順序和號碼的順序一樣就不用做這一步了。
2、取temp1中各個記錄的recn()，即記錄號，然后把編號減去記錄號
sele bh,val(bh-recn()) as aa from temp1 into curs temp2
結(jié)果就是：
Bh aa
1 0
2 0
3 0
5 1
6 1
7 1
9 2
10 2
15 6
16 6
3、對aa使用分組，取每組中最小的bh和的bh
sele min(bh) as minbh,max(bh) as maxbh from temp2 grou by aa into curs temp3
結(jié)果如下：
minbh maxbh
1 3
5 7
9 10
15 16
4、現(xiàn)在結(jié)果很明顯了，某個記錄的minbh跟它上面那個記錄的maxbh中間相差的，就是漏掉的號碼?，F(xiàn)在才使用scan endscan就容易了。
以上方法適用于數(shù)據(jù)量大、第三步的結(jié)果比源表少很多記錄的情況下才會發(fā)揮效果。因為scan的速度不能和Select - SQL相比，何況它還要作條件判斷。雖然這種方法使用了二條全遍歷的SQL命令，但這是沒過濾條件的SQL命令，速度是很快的，只是分組要多點時間而已。同時只適用于編號沒有重復(fù)的情況。如果編號有重復(fù)，那在第一步的時候，就要使用：
sele bh from temp1 goru by bh orde by bh into curs temp1
把多余的編號去掉。如果想了解那些編號是重復(fù)的，就可以使用：
sele bh,coun(bh) as aa from temp1 grou by bh orde by bh havi aa>1 into curs temp1
結(jié)果就是重復(fù)的編號，對應(yīng)的aa字段就是重復(fù)的次數(shù)。
在第二步，好象可以不用二條命令就可以得出結(jié)果了，只是暫時還沒想到，不知各位對些有什么看法。
上面這種方法有個缺點：就是使用了sum()函數(shù)，而它是只能統(tǒng)計數(shù)值型的數(shù)據(jù)，其它字符型、日期型等是不能統(tǒng)計的。所以如果想把一個表中所有記錄的產(chǎn)品名稱都串起來，變成一個字符串，那是不能的。但對于日期型，變通一下，有時還是可以使用的。
二、日期相減的結(jié)果是數(shù)值，從而可以使用sum()函數(shù)。例子二
編號 名稱 入庫日期
1 網(wǎng)卡 2000.09.02
1 網(wǎng)卡 2000.09.03
1 網(wǎng)卡 2000.09.05
2 vfp 2000.09.05
2 vfp 2000.09.06
說明：把編號和名稱相同，入庫日期相間為1的記錄合并為一條，并加入另一個表里，如結(jié)果：
編號 名稱 入庫日期 （表2和表1結(jié)構(gòu)一樣）
1 網(wǎng)卡 2000.09.03
1 網(wǎng)卡 2000.09.05
2 vfp 2000.09.06