Select命令参数很多，功能也很强大，适当的使用一些技巧，可以使程序更洁练，运行速度更快。但如果不是很强求速度的话，我认为太过复杂的命令对以后的维护工作带来困难。呵呵，有时我要理解自己写的命令都有点困难，实在是太复杂了。以下内容是工在工作过程中摸索出来的一些小技巧，希望对别人有帮助。 【在select的输出字段使用函数的技巧】 不要以为select的输出字段只能用“表.字段名”这样的格式，其实这里面大有文章可作。它可用使用函数、甚至自定义函数(当然这样会影响速度，搞不好还会影响结果的正确性，所以很复杂的自定义函数就不要用了)。而在这里最经常用的就是sum()、avg()、iif()之类的函数了。举个例子： 人员资料表ryb，内容如下： 姓名 职称 政治面貌 科室awfw 助工 党员 通讯科wafawe 高工 党员 机械科afawe 技术员 团员 财务科2wraw 无 无 人事科afwe 工程师 无 办公室…… 现要统计每个科室各有几名技术员、助工、工程师、高工、党员、团员,以下列表的形式打印出来 科室 技术员 助工 工程师 高工 党员 团员财务科 2 2 3 3 4 3…… 命令如下： sele 科室,sum(iif(职称='技术员',1,0)), sum(iif(职称='助工',1,0)), ;sum(iif(职称='工程师',1,0)), sum(iif(职称='高工',1,0)), ;sum(iif(政治面貌='党员',1,0)),sum(iif(政治面貌='团员',1,0)) ;from ryb grou by 科室 是不是很方便？ 这里还有一个更绝的例子： 表recdbf 〖性质〗分一般、重大、特大； 〖日期〗 要统计出某年的12个月内每个月每种性质的记录各有多少，若该月没有记录就为零。 结果：月份 一般 重大 特大 1 0 1 3 2 2 12 3 ...... 12 3 0 5 这跟上面那个例子有点不同，上例只是按表中科室来分类，而这里就不同，因为不是每个月都有案件的，但在报表里没有案件的月也要占一行。所以这里要建立一个辅助表:tempyf( yf N(2)),内有十二个记录，值为1至12，代表1－12月。 我先是老规则：sele mont(日期),iif(性质='一般',1,0) as 一般,iif(性质='重大',1,0) as 重大,iif(性质='特大',1,0) as 特大 ; from recdbf ; where year(日期)=?年份 ; into curs temp1 再用tempyf左联接临时表temp1，根据tempyf.yf分组统计。 但一看，结果好象不对，没有记录的月份不在结果当中，而且这两条select好象可以合而为一。这时，狐性大发，立决心要搞定它。 在看MSDN Help时，灵光一闪：把过滤条件从where移到联接条件中，直接使用sum命令统计iif的结果！几经波折，终于写出下面的命令 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; &&注意这里，on后面是可以加上其它条件的 GROUP BY tempyf.yf 结果完成符合要求！ 这个yy是指具体那一年，如果不指定的话，那就是把表中所有的记录算在一起。而且如果要指定具体那一年的话，那这个YEAR(日期) = ?yy的条件是不能放在where或者having里的，不信各位试试。 我最后的感想就是：那个sum命令是可以直接累加iif的结果，而且过滤条件从where移到on后会发生这么大的差别！在where时，是先联接统计后再过滤，结果把那些没有记录的月份也过滤掉了；而在on是先过滤再联接、统计，过滤结果中虽然没有记录的月份也不在其中，但因是左联接，也一样会以null代替，但给iif中的isnull给变成了0！啊，我真是太聪明了！哈哈哈。。。。。 注：其中那个 ON tempyf.yf = MONTH(recdbf.日期) AND YEAR(日期) = ?yy 在视图生成器中是不能直接做的，要用create view as生成(我就做不到，不知各位做不做得到) 【join联接中on 条件表达式的技巧】 标准的join字句是这样的 [DatabaseName!]Table [[AS] Local_Alias][[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOINDatabaseName!]Table [[AS] Local_Alias][ON JoinCondition ...] join这里是没得改变得了，但在“ON”这里就有文章可做了。既然ON后面跟得是一条逻辑表达式，那这条表达式就可以任写而不必一定要table1.字段1=table2.字段3这样了。在多表查询的时候，在这里适当的加些过滤条件又可省不少功夫。 例如做原材料管理时，每次进货、原材料不合格退货都有记录的。现在老板就想知道某段时间向某个客户的进了多少货及退了多少货，从而知道这个客户的原材料质量好不好。 表结构如下：客户表khb：khbh,khmx原材料表yclb：yclbh,yckmc,yclgg进货单jhd：jhdbh,jhrq,khbh(即这次是向那个客户进货的)进货单明细jhdmx：jhdbh,yclbh,jhsl退货单thd：thdbh,thrq,khbh(同上)退货单明细thdmx：thdbh,yclbh,thsl 【where字句的使用技巧】 这里头的技巧可就多了，真是学都学不完。 【union的使用技巧】 这个字句没什么，只是将几个select后产生的在结构完全一样的表加在地块，变成一个表。具体的例子如下(以下例子不计较这种设计方法的好坏，只是说明命令的使用)： 在做生产管理时，原材料的每笔进货、领用、报废、退货情况都要详细记录，老板随时都可能了解一个月内每种原材料的各种情况。而编程时对数据的规范化后，必然要用好几个表来记录这四种操作。现在为了更快的求出结果，命令当然是越少越好。各个表的结构如下： 进货单jhd：jhdbh,jhrq进货单明细jhdmx:jhdbh,yclbh,jhsl 领货单lhd：lhdbh,lhrq领货单明细lhdmx:lhdbh,yclbh,lhsl 报废单bfd：bfdbh,bfrq报废单明细bfdmx:bfdbh,yclbh,bfsl 退货单thd：thdbh,thrq退货单明细thdmx:thdbh,yclbh,thsl 原材料表yclb:yclbh,yclmc,yclgg 查询日期范围放在begindata,enddata里 为简便起见，只计算进货和退货，其它的照样做就行了。 Sele yclbh,sum(jhsl) as jhsl,100000.00-100000.00 as thsl ;From jhd join jhdmx ;On jhd.jhdbh=jhdmx.jhdbh ;Where betw(jhd.jhrq,begindata,enddata) ;Grou by yclbh ;Union ;Sele yclbh,100000.00-100000.00 ,sum(thsl) ;From thd join thdmx ;On thd.thdbh=thdmx.thdbh ;Where betw(thd.thrq,begindata,enddata) ;Grou by yclbh ;Into curs temp1 这样就会产生这样的结果 yclbh jhsl thsl 00001 3432 0 ┐00002 4234 0 │这些是第一节select产生的…… ┘00002 0 3423 ┐00003 0 4234 │这些是第二节select产生的…… ┘ 现在再对临时表temp1进行一次合计 sele yclbh,sum(jhsl) as jhsl,sum(thsl) as thsl ;from temp1 ;grou by yclbh ;into curs temp2 这个temp2就是结果了。 注1：如果要计算四种操作的话，就再加多两节select就行了。注2：在命令里使用100000.00-100000.00这样奇怪的表达式是因为select对那些要计算的、事先不能确定长度和类型的字段，它是根据运算过程中产生的第一条记录中，该字段的值来确定这个字段的类型和长度。所以以上那条命令，第一个记录中thsl它是不知道长度是多少的，如果直接用0的话，那它就以为这个字段是数值型，长度是1，没有小数位。这当然不行，所以就要用这样一个表达式，来使它知道这个字段有9位长，小数位是2。 【having的使用技巧】 其实这里没技巧可言，select的条件过滤的先后顺序是这样的(我是根据经验而猜得，不知对否)：先对join中的on表达式进行过滤，再到where，中间结果出来后再用having进行过滤，最后才把结果显示出来。所以说having是对select结果的最后一次过滤。它与where的分别就是where能够事先把不要的数据过滤掉，这样select里头就不用处理那么多的数据。但有些数据事先不知道要不要过滤，要根据结果才能确定，这时才用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日 重大 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 02 2 1 03 1 0 04 0 0 05 0 1 06 0 0 07 0 0 28 1 1 09 0 0 110 0 0 011 1 0 012 0 0 0 用where的结果如下： YF 一般 重大 特大1 1 0 02 2 1 03 1 0 05 0 1 07 0 0 28 1 1 0 用having的结果如下：YF 一般 重大 特大1 1 0 02 2 2 05 0 1 07 0 0 28 1 1 09 0 0 111 1 0 0 各位看到有什么不同吗？ on是把先把recdbf中不是2000年的记录过滤掉，剩下的就是2000年的了，再用tempyf去和它们进行外联接，其结果可用sele tempyf.*,recdbf.日期 ;from tempyf left join recdbf ;ON tempyf.yf = MONTH(recdbf.日期).AND.YEAR(日期) = ?yy; GROUP BY tempyf.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(……) 再看having1、第一步和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了。 【快速排名次】 考试排名次，一般有个要求，就是如果有两个人并列第一，那跟着的就不是第二名，而是第三名，即第二名跳空了。所以我想出以下这个方法。 面对大数据量的排名次，若用scan...endscan或do while这种方法，时间会非常长。1048576个记录中花了十多分钟才排了四十多万条记录。而我这种方法只需九十多秒(硬件：PII300(100*3),128M PC100,5.1G,VFP5.0)，方法如下： 1。数据库dele：rec_id c(7) 升序;name c(10);fs n(4,1) 降序;rec_orde n(7),文件大小为29M，索引文件16M2。建一个视图dele1SELECT Dele.fs, Dele.rec_orde;FROM dele;ORDER BY Dele.fs DESC3。根据视图dele1建立视图dele2SELECT DISTINCT Dele1.fs, MIN(Dele1.rec_orde) AS rec_orde;FROM dele1;GROUP BY Dele1.fs4。根据视图dele2和数据库dele建立视图dele3SELECT Dele.rec_id, Dele.rec_orde, Dele2.rec_orde, Dele.fs;FROM dele INNER JOIN dele2 Dele2 ;ON Dele.fs = Dele2.fs;ORDER BY Dele2.rec_orde准备工作做好，现在可以排序了：以上视图是事先做好的，不算入运行时间。 use dele1repl rec_orde with recn() allsele 0use dele3repl rec_orde_a with rec_orde_b allbrow这就是你想要的！ 【快速把表中重复的记录删除掉】 假设记录是否重复以用aa这个字段来判断，则对aa建立一个唯一索引。 set dele offclos dbf allsele 0use temp1set orde to aa &&打开唯一索引，那重复的记录就会看不到，dele all &&这里也不会把它们删除掉set orde to &&然后关闭索引，那些重复的记录就会出显，而且不带删除标志。set dele on &&把带删除标志的记录隐藏起来repl aa with -1 all &&剩下的就是重复且不带删除标志的记录，把它们统一做个标志，就是aa=-1reca all &&然后把所有删除的记录都恢复过来dele for aa=-1 &&所有AA=-1的记录就是重复的记录，把它们删除掉。 本人做了试验，环境：华硕P2B+PII300(100*3)+128M PC100+15G+VFP6.0+98SE，再加一个有163，840个记录的表，记录大小为14个字节，文件大小为2,457,960字节，索引文件大小为458,752字节。建立这个唯一索引只需5秒，而做完上面那段命令只需4.262秒，不知这样的速度算不算快？
评论