on、where、having的不同之处

第二节 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的结果如下：

YF 一般 重大 特大 1 1 0 0 2 2 2 0 5 0 1 0 7 0 0 2 8 1 1 0 9 0 0 1 11 1 0 0

各位看到有什么不同吗？

on是把先把recdbf中不是2000年的记录过滤掉，剩下的就是2000年的了，再用tempyf去和它们进行外联接，其结果可用：

sele tempyf.*,recdbf.日期 ; from tempyf left join recdbf ; ON tempyf.yf = MONTH(recdbf.日期).AND.YEAR(日期) = ?yy; orde by yf

来查看，这个中间结果出来后，再用isnull把空值的记录变成0或1，然后由sum去统计，结果就出来了。

而where呢：

1、 它是先把tempyf外联接recdbf,相当于sele tempyf.*,recdbf.* from tempyf left join recdbf on tempyf.yf=mont(recdbf.日期)；

2、 然后把不是2000的记录过滤掉，这里要注意的是，如果某个月没有记录的话，那在第一个步骤后日期那里是null值，这当然不是2000的记录，所以就给这个条件给过滤出去了，所以下一步的sum之后就只剩下那有记录的那个月了，象4、6月等几个月就没有了；

3、 然后进行sum(……)。

再看having：

1、第一步和where一样；

2、 第二步不同，它是先sum()，这里的sum可不管你是1999年还是2000的，先累加起来再说，这时，1999和2000年的2月份都有"重大"这个记录，sum的结果是2，这里用第三个步骤去分辨这个2之中那个是1999年的，那个是2000的，这当然分不清啦，所以也错了；

3、 根据步骤2来把2000的过滤出来。

所以on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。

根据上面的分析，可以知道where也应该比having快点的，因为它过滤数据后才进行sum，所以having是最慢的。但也不是说having没用，因为有时在步骤3还没出来都不知道那个记录才符合要求时，就要用having了。

在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢。

如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。

在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。

原文转自：http://www.ltesting.net