第二节 on、where、having的不同之处
这里有个例子来比较一下过滤条件放在on、where、having会有什么的不同之处:
表recdbf内容如下:还有一个tempyf的辅助表,记录12个月日期性质yf2000年7月3日特大12000年7月9日特大22000年9月3日特大31999年3月2日一般41999年3月4日一般52000年1月3日一般62000年2月1日一般72000年2月3日一般82000年3月4日一般92000年8月7日一般102000年11月2日一般111999年2月3日重大122000年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一般重大特大110022103100400050106000700281109001100001110012000
用where的结果如下:
YF一般重大特大110022103100501070028110
用having的结果如下:
YF一般重大特大11002220501070028110900111100
各位看到有什么不同吗?
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月等几个月就没有了;