作者: 康康(张初康)
第一节 select - SQL 的工作流程
再复杂的 SQL SELECT 命令，也是由一些基本的结构组成的。所以在看、去做一条很复杂的 SQL SELECT 命令时，要会把它一级一级的折分，最后折成最简单的，这样才容易理解。而这个折分过程，如果不熟悉 SQL SELECT 命令的工作流程，那就比较难折分了。
大体来说，它是先根据联接条件(即联接条件 on 中的表达式)，把几个的表合成一个临时表，然后根据 where 中的条件进行过滤，过滤出来的结果根据分组条件再把这个临时表分成一组一组，然后对分别对些组进行字段计算，最后又得出一个临时表，然后又根据 having 中的条件对这个临时表进行再次过滤，最后输入到指定的地方，如数组、表等。它中间生成的临时表对用户来说，是完全透明的，用户是不可能使用、也不能创建，它是由系统自己创建、自己使用、自己撤除，完全不受用户控制的。我举个例子：有二个表：提货单 thd、提货单明细 thdmx：
thd
提货单号 提货日期
thdbh thrq
01 2000/01/02
02 2000/01/15
03 2000/02/01
thdmx
提货单号 产品编号 提货数量
thdbh cpbh Thsl
01 001 5
01 003 15
01 005 12
02 001 13
02 002 14
02 005 20
03 002 14
现在有个要求：要统计 day1 =2000/01/01至 day2 =2000/01/20这段时间内，提货数量大于 10 的产品有那些，它们各自的总提货量是多少。命令如下：
sele cpbh,sum(thsl) ;
from thd join thdmx ;
on thd.thdbh=thdmx.thdbh.and.thsl>10.and.betw(thrq,day1,day2) ;
grou by cpbh ;
into curs temp1
为什么把 thsl>0 和 betw(thrq,day1,day2) 这二个条件表达式放在 on 那里，参见 on、where 与 having 的区别。它的工作流程：
首先根据 on 中的过滤条件，对所涉及的表进行预处理。过程如下：
两个表根据 thd.thdbh = thdmx.thdbh 进行合并，变成一个这样的临时表：
thdbh1 thrq thdbh2 Cpbh thsl
01 2000/01/02 01 001 5
01 2000/01/02 01 003 15
01 2000/01/02 01 005 12
01 2000/01/02 02 001 13
01 2000/01/02 02 002 14
01 2000/01/02 02 005 20
01 2000/01/02 03 002 14
02 2000/01/15 01 001 5
02 2000/01/15 01 003 15
02 2000/01/15 01 005 12
02 2000/01/15 02 001 13
02 2000/01/15 02 002 14
02 2000/01/15 02 005 20
02 2000/01/15 03 002 14
03 2000/02/01 01 001 5
03 2000/02/01 01 003 15
03 2000/02/01 01 005 12
03 2000/02/01 02 001 13
03 2000/02/01 02 002 14
03 2000/02/01 02 005 20
03 2000/02/01 03 002 14
合并是按照笛卡尔积进行计算的，即二个表都有 10 个记录，那积就会有 10*10 = 100 个记录，这是很历害的。但因为 on 条件中有过滤条件，所以 VFP 并不会这么笨，它会把符合这个条件的记录才放到临时表中的。这样，结果就少了很多记录了。结果出来后，再根据 on 后来的过滤条件 thsl>10.and.betw(thrq,day1,day2) 进行过滤，这样 thsl 大于 10 而且提货日期是在 day1 至 day2 的记录最后才出现在这一步的临时表中。
01 2000/01/02 01 003 15
01 2000/01/02 01 005 12
02 2000/01/15 02 001 13
02 2000/01/15 02 002 14
02 2000/01/15 02 005 20
现在轮到分组了。根据产品编号进行分组，它具体的分组方法我不知道是怎样，我想可能是这样的：
象在投票选举时点票那样，在上面那个临时表从头到尾扫一次，每经过一记录时，它就看一下，当前的产品编号是不是一个新的组，如果是就新增一个分组记号，相当于新增加一个被选举人，然后在它下面加上 thsl 的值，全部记录数完了，就看看有多少个分组标记，各个分组又有多少 thsl。结果就是以下的样子：
001 13
002 14
003 15
005 32
这就是这条命令的结果了。然后把它生成一个 cursor 表，命令就完成了。如果再深入一点，把要求改成某段时间内全部产品的提货情况，如果没有进货记录，那就是 0，一样要出现在结果表里。
这时，就涉及到三个表了：产品表 cpb、提货表 thd、提货明细表 thdmx。我们先用内联接来把这三个表联接起来。
sele cpb.cpbh,cpb.cpmc,sum(iif(isnull(thdmx.thsl),0,thdmx.thsl)) as thsl ;
from cpbh join thdmx ;
join thd ;
on cpbh.cpbh=thdmx.cpbh ;
on thdmx.thdbh=thd.thdbh.and.betw(thd.thrq,day1,day2) ;
grou by cpbh ;
into curs temp1
根据内联接的定义，即某个产品编号在产品表和提货明细表中都存在的记录，才会出现在结果表中，如果某种产品没有提货，那在提货明细表就没有这个记录，这样，也就不会出现在结果表中。那样就符合要求中的"全部产品"这个条件了。所以我们要把 产品表 左联接 提货明细表，这样不管这种产品有没有提货，它都会出现在结果表中，只是以 null 的值出现。但这个现象可以消除的。
命令过程也和上一个要求那样，先进行联接，只是这条命令的中间临时表比上面更大，因为是三个表的记录数相乘。但经过联接条件过滤后，就会剩下这些内容：
001 02 2000/01/15 02 001 13
002 02 2000/01/15 02 002 14
003 01 2000/01/02 01 003 15
004 null null null null null
005 01 2000/01/02 01 005 12
005 02 2000/01/15 02 005 20
然后也一样进行分组，分组时的判断过程也一样，只是在累加的时候有点不同：
sum(iif(isnull(thdmx.thsl),0,thdmx.thsl))，是先用 isnull(thdmx.thsl) 检查 thsl 是不是 null，如果是，则 iif() 就返回 0，如果不是，则返回 thdmx.thsl。然后外层的 sum() 就根据 iif() 返回的数值进行累加，最后做为这个分组的累加值。
还有一种使用方法，说出来也可以加深命令当中的 sum() 函数的处理过程。
人事表rsb：
姓名xm 年龄age
张三 25
李四 32
王五 28
现在想统计一下各个年龄段(20-30,31-40)的人数是多少。
sele sum(iif(betw(age,20,30),1,0) as _20-30,sum(iif(betw(age,31,40),1,0) as _31-40 ;
from rsb ;
grou by zc ;
into curs temp1
因为这条命令没有过滤条件、联接，所以不需事先预处理，一来就进行分组。和投票中点票一样，在"黑板"上写划出三列：
zc _20-30 _31-40
这样，在 rsb 中从头扫到尾时，每经过一记录时，都用 iif(betw(age,20,30),1,0) 检查这个人的年龄是不是处于 20-30，如果是就在_20-30 这一列下面加一横，否则就不加。第二个 iif() 也这样处理。如果当前的年龄是 53，那二个 iif() 都是返回 0，即二列都不加，相当于废票。全部记录都点完了，然后就用 sum() 进行合计了，结果就出来了。
因为二个 sum() 都是扫描完后再合计的，它不象 sum 命令。sum 命令会移动记录指针，而 sum() 函数不会，所以不必怕使用这个函数会造成不良后果。而且二个 iif() 都有自己的判断条件，两者不互相重合，所以一个记录不会重复计算(除非你的命令设计错了)。
如果使用 union 参数把两条运算结果的格式一模一样的命令合在一起，那也是一样的。它是把其中的每一节 select 命令单独运行(它单独运行时的运行流程跟上面说的一样)，最后才把每一节的结果首尾相接后，再根据最后那节的 orde by 进行排序。所以一条带 union 的 SQL SELECT 命令，只能有一个 orde by。而每一节 select 命令，却可以有自己的 grou by，它自己的 grou by 只对这一节有影响，是不会影响到其它节的运算的。更不会对最后结果有影响。
这里举个例子：我想统计一段时间内的提货、进货情况。这里要涉及到 5 个表：产品表、提货表、提货明细表、进货表、进货明细表：
sele cpbh,sum(thdmx.thsl) as thsl,100000-100000 as jhsl ;
from thd join thdmx ;
on thd.thdbh=thdmx.thdbh ;
grou by cpbh ;
union ;
sele cpbh,0,sum(jhdmx.jhsl) ;
from jhd join jhdmx ;
on jhd.jhdbh=jhdmx.jhdbh ;
grou by cpbh ;
orde by cpbh ;
into curs temp1
如果看了上面的解释，应该可以理解每一节 SQL SELECT 命令的意思。这里要说的是：
1、union 要求前后两节 SQL SELECT 命令产生的表，在结构上要完全一样，包括字段的顺序也要一样。所以为了达到这个要求，在第一节，就要人为建立一个字段 jhsl，而在第二节命令，也要建立一个字段 thsl 以对应。
2、用 SQL SELECT 产生的表，它不象 crea table 那样可以直接指定字段的类型、长度，而是在根据生成的临时表中第一个记录的长度来确定的。所在在第一节，如果不使用 100000-100000 而是直接使用 0，这样产生的 jhsl 这个字段，它的长度就只有 2 个字节了。所以只有使用这种方法，才能使得这个字段的长度有 7 字节。在字符串也有这样情况，如果第一个记录的长度是 12 个字节，那以后的记录中，超过 12 个字节的内容就会给它去掉，这就是为什么有时在结果表中会出现字符串不完整的情况。解决方法也差不多，在字段列表那里人空加几个空格去。
--------------------------------------------------------------------------------
第二节 on、where、having的不同之处
这里有个例子来比较一下过滤条件放在 on、where、having 会有什么的不同之处：
表 recdbf 内容如下： 还有一个 tempyf 的辅助表，记录 12 个月
日期 性质 yf
2000年7月3日 特大 1
2000年7月9日 特大 2
2000年9月3日 特大 3
1999年3月2日 一般 4
1999年3月4日 一般 5
2000年1月3日 一般 6
2000年2月1日 一般 7
2000年2月3日 一般 8
2000年3月4日 一般 9
2000年8月7日 一般 10
2000年11月2日 一般 11
1999年2月3日 重大 12
2000年2月3日 重大
2000年5月2日 重大
2000年8月9日 重大
现在的要求是要统计 yy 年中十二个月的事故记录中，一般、重大、特大各有多少。如果没有事故的，则以 0 表示。
我们首先要把今年的记录过滤出来，过滤条件就是 YEAR(日期)=?yy，然后按月份分组统计。
这样一来，如果某个月没有事故记录，那分组后的结果就没有该月的记录，这样不符合要求。所以做个临时表 yf，该表有十二个记录，分别代表 1 至 12 月，用它来左联接 recdbf，这样，即使某个月没有事故记录，也会出现在最后的结果当中，只是以 null 的形式出现罢了。但我们可以使用 isnull() 函数来判断它是不是 null 值，如果是，则 iif() 会把它变为 0，然后交与 sum() 进行统计。
总体设想搞好后，现在就开始写命令了。开始之前先说明：tempyf.yf = MONTH(recdbf.日期)是 yf 表与 recdbf 表的联接条件，是一定要在on的，这个不在讨论范围。我们要讨论的是 YEAR(日期) = ?yy 这个条件放在什么地方会有什么样的结果。
首先把过滤条件放在 on 这里：
SELECT tempyf.*,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("一般",recdbf.性质)=0,0,1)) AS 一般,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("重大",recdbf.性质)=0,0,1)) AS 重大,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("特大",recdbf.性质)=0,0,1)) AS 特大;
FROM tempyf LEFT OUTER JOIN recdbf ;
ON tempyf.yf = MONTH(recdbf.日期).AND.YEAR(日期) = ?yy;
GROUP BY tempyf.yf
其中 yy=2000，表示统计 2000 年的数据。
用 where 的命令如下：
SELECT tempyf.*,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("一般",recdbf.性质)=0,0,1)) AS 一般,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("重大",recdbf.性质)=0,0,1)) AS 重大,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("特大",recdbf.性质)=0,0,1)) AS 特大;
FROM tempyf LEFT OUTER JOIN recdbf ;
ON tempyf.yf = MONTH(recdbf.日期);
GROUP BY tempyf.yf ;
where YEAR(日期) = ?yy &&注意，条件从 on 移到这里来了
用 having 的命令如下：
SELECT tempyf.*,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("一般",recdbf.性质)=0,0,1)) AS 一般,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("重大",recdbf.性质)=0,0,1)) AS 重大,;
SUM(IIF(ISNULL(recdbf.日期).OR.AT("特大",recdbf.性质)=0,0,1)) AS 特大;
FROM tempyf LEFT OUTER JOIN recdbf ;
ON tempyf.yf = MONTH(recdbf.日期);
GROUP BY tempyf.yf ;
having YEAR(日期) = ?yy &&注意，条件从 on 移到这里来了
on 的结果如下，这是正确的：
YF 一般 重大 特大
1 1 0 0
2 2 1 0
3 1 0 0
4 0 0 0
5 0 1 0
6 0 0 0
7 0 0 2
8 1 1 0
9 0 0 1
10 0 0 0
11 1 0 0
12 0 0 0
用 where 的结果如下：
YF 一般 重大 特大
1 1 0 0
2 2 1 0
3 1 0 0
5 0 1 0
7 0 0 2
8 1 1 0
用 having 的结果如下：
作者在 2015-01-16 14:52:30 发布以下内容