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