<% dim ModuleName,InfoID,ChannelShortName,CorrelativeArticle,InstallDir,ChannelDir,Keyword,PageTitle,ArticleIntro,Articlecontent Keyword=stripHTML("数据库.FoxPro.基础") PageTitle=stripHTML("on、where、having的不同之处") ArticleIntro=stripHTML("") Articlecontent=stripHTML("第二节 on、where、having的不同之处   这里有个例子来比较一下过滤条件放在on、where、having会有什么的不同之处: 表recdbf内容如…") ModuleName = stripHTML("classical") InfoID = stripHTML("28939") ChannelShortName=stripHTML("文章") InstallDir=stripHTML("http://www.77169.com/") ChannelDir=stripHTML("classical") %> on、where、having的不同之处 - 华盟网 - http://www.77169.com
您现在的位置: 华盟网 >> 知识库 >> 数据库 >> FoxPro >> 基础教程 >> 正文

on、where、having的不同之处

2005/8/29 作者:unknown 来源: unknown
导读 <% if len(ArticleIntro)<3 then Response.Write Articlecontent 'Response.Write "Articlecontent" else Response.Write ArticleIntro 'Response.Write "ArticleIntro" end if %>

第二节 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进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里。