第五節 group by分組的應用

首先先說說分組是怎樣工作的。舉個例子：

表：temp1有以下記錄：

bhslaaa1aaa4ccc2bbb5aaa9bbb7

現在要統計一下temp1有中多少種編號，各種編號的總數量又是多少。很明顯，這是使用分組。

sele bh,sum(sl) as total_sl ;  from temp1 ;  grou by bh ;  orde by bh ;  into curs temp2

命令的運行過程就象投票選舉中的點票過程一樣。它中間到底是采用什么樣的技術來儲存中間結果我不知道，但不這重要，我現在假設它褂昧偈北?實際上我想不大可能)，這樣對結果沒影響，但又容易理解。

1、是逐個掃描記錄。每遇到一個新的編號，就為這個編號建立一個臨時表(再次重申，使用臨時表只是我的假設布局，真正的處理方法我不知道)，然后把這個記錄的內容放進對應的臨時表中去。如果不是新編號，就直接把記錄內容放進對應的臨時表中去。

2、全部記錄掃描完了，各種編號對應的臨時表也產生了，現在就是對每一個臨時表采用sum()進行統計了。比如編號為"aaa"的臨時表是cursor1，那它的內容就是：

aaa1aaa4aaa9

現在進行sum()統計了，它的效果就相當于sele bh,sum(sl) as total_sl from cursor1。每一個編號的臨時表都要進行這樣的sum()統計，然后把每個編號的統計結果聯合起來，就想使用union一樣，最后得出的結果就是：

aaa14ccc2bbb12

3、然后再給bh排序，結果就是：

aaa14bbb12ccc2

看了上面那個例子，再看下面這條命令，想必也可以理解了吧：

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 

產品表左聯接提貨明細表，這樣沒有提過的產品，在聯接后的臨時表中，對應的提貨數量就是個null值。到了分組，第一步完成后，生成的臨時表中也仍然有null值，所以在第二步進行sum命令時，就要使用iif(isnull())來過濾了。過濾的結果就是把null值改為0，其它的不變，然后再由sum進行匯總。最后第三步再排序，結果就出來了。

以上是分組的最基本用法。現在說說分組的一些古怪用法。

一、使用recn()。例子一：

有種情況，一大段本來是應該連續的號碼，但中間地漏掉了一些號碼，現在要把這些漏了的號碼找出來。恐怕很多人，一來就是使用scan endscan，再加上一些中間變量以作為判斷依據，最后寫成的代碼有一大段，運行起來慢得要死。但如果采用以下這種方法，我想即使它不是最好，但也是很好的了。數據如下：

1、6、3、5、2、7、9、10、15、16

在這些數據當中，要找出漏掉了的4、8、11、12、13、14。

1、首先排序

sele bh from temp1 orde by bh into curs temp1

結果是：

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

結果就是：

bhaa10203051617192102156166

3、對aa使用分組，取每組中最小的bh和最大的bh

sele min(bh) as minbh,max(bh) as maxbh from temp2 grou by aa into curs temp3

結果如下：

minbhmaxbh13579101516

4、現在結果很明顯了，某個記錄的minbh跟它上面那個記錄的maxbh中間相差的，就是漏掉的號碼。現在才使用scan endscan就容易了。

以上方法適用于數據量大、第三步的結果比源表少很多記錄的情況下才會發揮效果。因為scan的速度不能和select - sql相比，何況它還要作條件判斷。雖然這種方法使用了二條全遍歷的sql命令，但這是沒過濾條件的sql命令，速度是很快的，只是分組要多點時間而已。同時只適用于編號沒有重復的情況。如果編號有重復，那在第一步的時候，就要使用：

sele bh from temp1 goru by bh orde by bh into curs temp1

把多余的編號去掉。如果想了解那些編號是重復的，就可以使用：

sele bh,coun(bh) as aa from temp1 grou by bh orde by bh havi aa>1 into curs temp1

結果就是重復的編號，對應的aa字段就是重復的次數。

在第二步，好象可以不用二條命令就可以得出結果了，只是暫時還沒想到，不知各位對些有什么看法。

上面這種方法有個缺點：就是使用了sum()函數，而它是只能統計數值型的數據，其它字符型、日期型等是不能統計的。所以如果想把一個表中所有記錄的產品名稱都串起來，變成一個字符串，那是不能的。但對于日期型，變通一下，有時還是可以使用的。

二、日期相減的結果是數值，從而可以使用sum()函數。例子二

編號名稱入庫日期1網卡2000.09.021網卡2000.09.031網卡2000.09.052vfp2000.09.052vfp2000.09.06

說明：把編號和名稱相同，入庫日期相間為1的記錄合并為一條，并加入另一個表里，如結果：

編號     名稱   入庫日期        （表2和表1結構一樣）1網卡2000.09.031網卡2000.09.052vfp2000.09.06

要完成上面的要求，用兩個步驟：

1、sele * from 表1 orde by 名稱,入庫日期 into curs temp12、sele 編號,名稱,max(入庫日期), 入庫日期-recn() as dd;   from temp1 ;   grou by 名稱,dd ;   into curs temp2

問題的關鍵是那個"入庫日期-recn() as dd"，因為已經按日期按好了順序，所以9月2號減1和9月3號減2的結果都是相同的，但9月5號減3就是另外一個數了，所以這樣就把這些日期分開了，就可以用分組了。假設表內容如下：（xm是字符類型，其中的1代表上午上班，2上午下班，3、4如此類推）

這個例子，到在處理第二步的時候，其實就跟第一個例子一樣了，都是把一個字段減去記錄號，然后根據結果進行分組。

例子三：某員工某月的打卡記錄temp1如下：

打卡時間sj項目xm狀態zt2000/09/1308:01:00am1on time2000/09/1312:00:00am2on time2000/09/1302:00:00pm3on time2000/09/1305:59:00pm4early 2000/09/1208:00:00am1on time2000/09/1212:00:00am2on time2000/09/1202:00:00pm3on time2000/09/1206:00:00pm4on time2000/09/1408:00:00am1on time2000/09/1412:00:00am2on time2000/09/1402:01:00pm3later 2000/09/1406:00:00pm4on time2000/09/1508:00:00am1on time2000/09/1512:00:00am2on time2000/09/1502:00:00pm3on time2000/09/1506:00:00pm4on time

xm中的1、2、3、4分別代表早上上班、早上下班、下午上班、下午下班。zt中on time表示準時，early表示早退，later表示遲到。在以上數據，把每天的上下班狀態用以下的格式列出來：

日期上午上班時間上午下班時間下午上班時間下午下班時間上午上班狀態下午狀態

很明顯，這是根據打時間進行分組。如果使用：

ele day(sj) as dd ,     iif(xm='1',sj,{}) as 上午上班時間, ;     iif(xm='2',sj,{}) as 上午下班時間 ;  　from temp1 ;  grou by dd 

這個方法不行，原因我詳細說一下，可能會有點羅嗦。先說說13號這天的數據。在第一個記錄，在第一個iif()，xm=1，上午上班時間就是早上八點。到了第二個記錄，xm=2，所以第一個iif()的就是{}，即空白日期。到了第三、四個記錄，上午上班時間都是空白日期，所以到最后的結果就是13號這天，上午上班時間是空白日期而不是早上八點！其他日期、除了下午下班時間之外其他時間都是如此。按前面說的分組過程，是先對每天的記錄進行分組，然后按順序一個個記錄的計算iif()的結果，每計算一次iif()，都更新上午上班時間的值，這樣在每天四個上下班時間中，前面三個記錄都不起作用，只有第四個才起作用了。

那是不是就不能用分組了呢？那又不是，我們有sum()函數，在sum()函數里，它可以累計前面那三個記錄的值，但要是數值型的字段才行。而現在卻是日期型，所以我們要轉換一下。命令如下：

先定義一個全程變量：initsj={^1900-01-01,00:00:00}，即1900年1月1日零時，用它來做基準時間。

select day(sj) as dd,;  initsj+sum(iif(xm="1",sj-initsj,0)) as sj1,;  initsj+sum(iif(xm="2",sj-initsj,0)) as sj2,;  initsj+sum(iif(xm="3",sj-initsj,0)) as sj3,;  initsj+sum(iif(xm="4",sj-initsj,0)) as sj4,;  iif(sum(iif(xm="1".and.zt="on time",1,0))=1,"on time","no time"),;  iif(sum(iif(xm="3".and.zt="on time",1,0))=1,"on time","no time");from 數據1!temp5;group by 1

結果如下：(為了看得更清楚，我省略了兩個字段sj2,sj3，dd是表示日期)

ddsj1sb3exp_6exp_7122000/09/12 08:00:00 am2000/09/12 02:00:00 pmon timeon time132000/09/13 08:01:00 am2000/09/13 02:00:00 pmno timeon time142000/09/14 08:00:00 am2000/09/14 02:01:00 pmon timeno time152000/09/15 08:00:00 am2000/09/15 02:00:00 pmon timeon time

和第一條命令相比，它不同的地方是：在分組之后，在13號這天的分組里，第一個記錄的xm=1，所以iif()的結果是sj-initsj，即這天的上午上班時間跟基準時間的差(是一個數值型的)。到了第二個記錄xm=2，iif()的結果是0，第三、四個記錄的iif()都是0，最后sum()把sj-initsj、0、0、0這四個數值中起來，結果還是sj-initsj，也就是第一個記錄跟基準時間的差，然后再加上基準時間，也就是這天的上班時間了。

這條命令的奧妙就在于日期可以相減，結果是一個數值，然后用sum()進行累加；而日期加一數值，結果還是日期。在求早上狀態時(xm='1')，如果不是早上上班的時間，就累加零，否則就累加上班時間與基準時間的差。最后把結果再加上基準時間又得回原來的上班時間。

而字符那里，如果直接使用第一條命令那種做法，也是不行的。原因也一樣，前面三個記錄的結果都讓第四個記錄的值給覆蓋了。因本例特殊點，具有唯一性，所以還可以sum()+iif()的方法。但sum()不可以處理字符串，所以要用iif()轉換為數值型。

工作原理跟剛才計算時間那樣，用內部的iif()把xm="1".and.zt="on time"即上午準時上班的記錄設為1，而其它的時間或上午不是準時上班的就是0，然后用sum()累加，最后又用外面的iif()對sum()的結果進行判斷，如果結果是1，就表示上午是準時上班。因為只有一個上午上班記錄(xm='1')，而只有zt='on time'才表示準時上班。

在決定是否用分組前，應先確定以哪些定段作為分組依據。分組依據確定后，就要檢查一下，你認為某些記錄是應該在同一個分組內的，但如果直接就去分組的話，這些記錄又不是同一個分組內(看看第一和第二個例子)，就要想辦法找出這些記錄有什么共同點，然后根據這些共同點轉換一下，得出一個相同的中間值(第一個例子就是減去各自的記錄號從而找出共同點)。然后才根據這個共同點進行分組。