第五节 group by分组的应用
首先先说说分组是怎样工作的。举个例子：
表：temp1有以下记录：
bh sl aaa 1 aaa 4 ccc 2 bbb 5 aaa 9 bbb 7
现在要统计一下temp1有中多少种编号，各种编号的总数量又是多少。很明显，这是使用分组。
sele bh,sum(sl) as total_sl ; from temp1 ; grou by bh ; orde by bh ; into curs temp2
命令的运行过程就象投票选举中的点票过程一样。它中间到底是采用什么样的技术来储存中间结果我不知道，但不这重要，我现在假设它使用临时表(实际上我想不大可能)，这样对结果没影响，但又容易理解。
1、是逐个扫描记录。每遇到一个新的编号，就为这个编号建立一个临时表(再次重申，使用临时表只是我的假设布局，真正的处理方法我不知道)，然后把这个记录的内容放进对应的临时表中去。如果不是新编号，就直接把记录内容放进对应的临时表中去。
2、全部记录扫描完了，各种编号对应的临时表也产生了，现在就是对每一个临时表采用sum()进行统计了。比如编号为"aaa"的临时表是cursor1，那它的内容就是：
Aaa 1 Aaa 4 Aaa 9
现在进行sum()统计了，它的效果就相当于sele bh,sum(sl) as total_sl from cursor1。每一个编号的临时表都要进行这样的sum()统计，然后把每个编号的统计结果联合起来，就想使用union一样，最后得出的结果就是：
aaa 14 ccc 2 bbb 12
3、然后再给bh排序，结果就是：
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
产品表左联接提货明细表，这样没有提过的产品，在联接后的临时表中，对应的提货数量就是个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
结果就是：
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
结果如下：
minbh maxbh 1 3 5 7 9 10 15 16
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.02 1 网卡 2000.09.03 1 网卡 2000.09.05 2 vfp 2000.09.05 2 vfp 2000.09.06
说明：把编号和名称相同，入库日期相间为1的记录合并为一条，并加入另一个表里，如结果：
编号 名称 入库日期 （表2和表1结构一样） 1 网卡 2000.09.03 1 网卡 2000.09.05 2 vfp 2000.09.06
要完成上面的要求，用两个步骤：
1、sele * from 表1 orde by 名称,入库日期 into curs temp1 2、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 状态ZT 2000/09/1308:01:00AM 1 On time 2000/09/1312:00:00AM 2 On time 2000/09/1302:00:00PM 3 On time 2000/09/1305:59:00PM 4 Early 2000/09/1208:00:00AM 1 On time 2000/09/1212:00:00AM 2 On time 2000/09/1202:00:00PM 3 On time 2000/09/1206:00:00PM 4 On time 2000/09/1408:00:00AM 1 On time 2000/09/1412:00:00AM 2 On time 2000/09/1402:01:00PM 3 Later 2000/09/1406:00:00PM 4 On time 2000/09/1508:00:00AM 1 On time 2000/09/1512:00:00AM 2 On time 2000/09/1502:00:00PM 3 On time 2000/09/1506:00:00PM 4 On 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是表示日期)
DD SJ1 SB3 Exp_6 Exp_7 12 2000/09/12 08:00:00 AM 2000/09/12 02:00:00 PM On time On time 13 2000/09/13 08:01:00 AM 2000/09/13 02:00:00 PM NO time On time 14 2000/09/14 08:00:00 AM 2000/09/14 02:01:00 PM On time NO time 15 2000/09/15 08:00:00 AM 2000/09/15 02:00:00 PM On time On 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'才表示准时上班。
在决定是否用分组前，应先确定以哪些定段作为分组依据。分组依据确定后，就要检查一下，你认为某些记录是应该在同一个分组内的，但如果直接就去分组的话，这些记录又不是同一个分组内(看看第一和第二个例子)，就要想办法找出这些记录有什么共同点，然后根据这些共同点转换一下，得出一个相同的中间值(第一个例子就是减去各自的记录号从而找出共同点)。然后才根据这个共同点进行分组。
这天的数据。在第一个记录，在第一个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是表示日期)
DD SJ1 SB3 Exp_6 Exp_7 12 2000/09/12 08:00:00 AM 2000/09/12 02:00:00 PM On time On time 13 2000/09/13 08:01:00 AM 2000/09/13 02:00:00 PM NO time On time 14 2000/09/14 08:00:00 AM 2000/09/14 02:01:00 PM On time NO time 15 2000/09/15 08:00:00 AM 2000/09/15 02:00:00 PM On time On 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，而其
