EXCELOLEDB查询下的SQL函数整理whsfhwmWord文档下载推荐.docx

1、13. Str（number） 16（三） 字符串处理函数 171. Trim（string） 172. Ltrim（string） 173. Rtrim（string） 174. Len（string） 175. Left（string, x） 176. Right（string, x） 177. Mid（string, start,x） 178. Ucase（string） 189. Lcase（string） 1810. Space（x） 1811. Asc（string） 1812. AscB（string） 1813. AscW（string） 1814. Chr（charcode）

2、 1815. ChrW（charcode as long） 1816. String（number,string） 1817. LeftB（String, Length As Long） 1818. RightB（String, Length As Long） 1919. MidB（String, Start As Long, Length） 1920. LenB（Expression） 1921. Format（expression, format, firstdayofweek, firstweekofyear） 1922. StrComp（string1, string2, compar

3、e） 2023. StrConv（string, conversion, LCID） 21四、 数学函数 221. Sin（X）、Cos（X）、Tan（X）、Atan（x） 222. Log（x） 223. Exp（x） 224. Abs（x） 返回绝对值。 225. Int（number）、Fix（number） 226. Sgn（number） 227. Sqr（number） 228. VarType（varname） 229. Rnd（x） 2210. ROUND（c,decimals） 22五、 测试函数 231. IsNumeric（x） 232. IsDate（x） 233. I

4、sEmpty（x） 234. IsError（expression） 235. IsNull（expression） 236. IsObject（identifier） 23六、 日期时间函数 231. 日期加减：DateAdd函数 232. 计算两个日期的差DateDiff函数： 243. 提取日期中的部分内容DatePart函数：4. 用独立的部件生成日期DateSerial函数： 265. 用字符串表达式生成日期：DateValue（stringexpression） 266. Now（） 267. Date（） 268. Time（） 269. Timer（） 2610. TimeSe

5、rial（hour, minute, second） 2611. DateDiff（interval, date1, date2, firstdayofweek, firstweekofyear） 2712. Second（time） 2713. Minute（time） 2714. Hour（time） 2715. Day（date） 2716. Month（date） 2717. Year（date） 2718. Weekday（date, firstdayofweek） 27七、 流程控制函数 271. Iif（）和Switch（） 27八、 结束语 28一、 前言1. 背景简介一直想对

6、EXCEL 2003中SQL语句里的可用函数进行专门的归纳整理，今天终于与大家见面了。虽然目前EXCEL 2007/2010使用者越来越多，但无疑EXCEL 2003依然有着大量的使用人群，所以我才觉得很有必要对此进行整理，方便SQL初学者，希望他们能够少走一些弯路，节约一点时间来学习更多的精彩内容。相信对于使用高版本的朋友来说，本文也是有一定的借鉴作用的。构成本文的内容，有相当部分是来自于EH培训中心SQL初级培训班的培训过程，有引自EH技术论坛的内容，也有来自微软提供的帮助文档，当然还有本人自己钻研的一点点心得体会，所以这里主要是一种归纳整理。由于本人水平所限，学习SQL时间也不长，所以有

7、许多地方理解还比较肤浅，也难免有所疏漏或谬误，欢迎大家批评指正，或给与补充。本文中，如果没有特别说明，则所提到的SQL函数都是已经经过本人在EXCEL2003中OLE DB环境下测试并通过的；文中提到的示例语句一般使用蓝色字体，由于示例太多的话，将会让EXCEL文件过于庞大，故只在EXCEL文件“EXCEL-OLE DB之SQL函数示例（whsfhwm整理）.xls”里提供了一部分实例，方便大家进行测试，文中出现的示例，而上述EXCEL文件中又没有的，完全可以自己做测试。另外，由于SQL语句的语法对于大小写不敏感，所以本文中并没有对SQL语句的书写进行严格规范，这一点请大家多多包涵。2. 适用

8、环境：本文介绍的内容适用的环境为：EXCEL 2003 OLE-DB环境下的SQL查询。本文内容重点在于介绍SQL函数，而非SQL语句。3. 技巧介绍介绍SQL函数之前，先介绍一下本人在测试SQL语句中的几个小技巧： 编辑技巧：（虽然不是本人首创，但接触SQL多了，自然而然就需要这样做了）使用记事本或其他类似小巧的编辑工具来编辑SQL语句。通常，你会感觉到编辑查询对话框中进行编辑SQL语句实在是不方便，因此推荐在记事本中进行编辑，好处有两点：一是可以方便的将数据源的标题行复制过来，然后用替换的方法将字段名称之间转化为“,”，这个是SQL语句语法的要求，这样复制过来的字段名称还可以避免自己录入出

9、错的可能性；二是可以防止在所书写SQL语句有错误的情况下丢失刚才的语句，起到临时书写备份的作用，便于自己对错误语句的检查。 首次创建查询表技巧：第一次创建导入外部数据的查询表时，不得不通过菜单-导入外部数据-导入数据-选择数据源文件-出现“编辑OLE DB查询”对话框时，此时一般就不要匆忙将自己书写的SQL查询语句录入到命令文本中，而是采用默认的方式生成查询表，这样做的好处就是避免自己所写的SQL语句有误，不产生任何结果，然后下一次操作又要重复上面的步骤，效率显然较低；所以建议采用默认方式，然后再通过右键快捷菜单打开编辑查询对话框进行修改测试。 复制SQL查询表技巧：针对同一数据源创建多个SQ

10、L查询表时，第二个及以后的查询表可以不用通过菜单操作，而直接把第一个SQL查询表的全部区域进行复制（显然比通过菜单操作要快得多），然后修改SQL语句即可。注意：当你的被复制的SQL查询表只返回一个值时，也就是只有一个字段名，一个结果值，显示上只有一列两行，实际要复制的时候必须复制一列3行，即把结果值下面的那个单元格也一起复制，否则复制过去的就不是查询表，右键点击就不会有“编辑查询”菜单（经本人测试，EXCEL2007/2010版本创建的SQL查询表不存在此问题）。 调试复杂SQL语句技巧（比如用到嵌套查询或联合查询或子句较多）：可以将SQL语句分割成多个独立的查询分语句或去掉一些复杂子句，然后

11、逐一测试分语句或逐步添加子句，这样便于发现问题，或观察中间结果是否与自己的期望结果一致。 注意检查源表字段的数据类型：这个其实不叫技巧，但需要引起重视，尤其是出现古怪的结果的时候。尽量采用规范的表格数据结构，同一个字段的数据类型要注意保持一致，否则可能出现意想不到的结果，超出你的期望。4. SQL函数分类EXCEL2003中的SQL函数主要有如下几类（共有88个）： 聚合函数（也有称作“合计函数”、“聚集函数”等其他叫法的，我们主要注重学习用法。） 字符串相关函数 日期时间函数 数学函数 测试函数 流程控制函数二、 聚合函数这一部分主要参考了Microsoft Jet SQL语言参考中的内容，

12、并结合本人的测试示例和一些亲身体会来介绍。聚合函数主要有： Sum 函数 Count 函数 Avg 函数 Min, Max 函数 First, Last 函数 StDev, StDevP 函数 Var 和 VarP 函数使用聚合函数的查询必须要有from子句。5. Sum 函数 返回值：包含在指定查询字段中一组值的总计。语法: Sum（expr）其中 expr 代表一个字符串表达式，它或者标识一个字段，该字段包含要计算的数据；或者是一个表达式，它使用此字段中的数据来执行计算。 expr中的运算对象可能包括一个表字段名，一个常数或一个函数（可能是内在的，也可能是用户自定义的，但不是 SQL 聚合

13、函数）。说明：Sum 函数会忽略包含 Null 字段的记录。可以在一个查询表达式之中使用 Sum 函数。你也可以在具备 SQL 特性的 QueryDef 对象中或在创建基于SQL查询的 Recordset 对象时使用这一表达式。示例：示例1-1：查询各省份的发货数量小计：select 省份,sum（数量） as 数量小计 from 发货清单$ group by 省份示例1-2：统计发货总数量： select sum（数量） as 总数量 from 发货清单$示例1-3：统计湖南省2009年发货总数量：select sum（数量） as 2009年湖南发货总屏数 from 发货清单$ where

14、 省份=湖南 and 发货日期 between #2009-1-1# and #2009-12-31#在EXCEL2003中，上述示例1-2、示例1-3 的返回值虽然都是一个，只有2个单元格（包括字段名称）有显示内容，但所在的SQL查询结果占用了3个单元格（包括第3行所在同列的单元格）。此外，sum（）函数和后面介绍的count（）函数在排名等其他方面的高级应用，请大家参考Scarlett_88讲师的精华帖：SQL&Excel结合经典剖析：47期基础题呀，几多疑惑，几多玩味。我们来细细品一品。6. Count 函数计算从查询返回的记录数。语法：Count（expr）expr 中的运算对象可能包

15、括一个表字段名，一个常数或一个函数（可能是内在的，也可能是用户自定义的，但不是 SQL合计函数）.您可以计算任何种类的数据，包含文本数据。可以使用 Count 计算下一级查询的记录数。例如，可以使用 Count 计算运往指定国家的货物订单数量。虽然 expr 能执行一个字段上的计算，但 Count 只是简单计算记录的数量。并不管记录中保存的是什么值。Count 函数不数带有 Null 字段的记录，除非 expr是星号 （*） 通配符dadefWildcardCharacters.如果使用星号，Count 将计算所有记录的总量，包括有 Null 的字段的记录。Count（*） 比 Count （

16、Column Name） 快得多。不要将星号放在引号 （ ） 中。示例2：计算“发货清单”表中的省份个数：SELECT count （*） as 省份个数 FROM（ SELECT distinct 省份 from 发货清单$ ） 上例中的distinct关键字使得嵌套查询子句获得所有不重复的省份，然后通过外层查询中的count（*）计算省份个数。如果 expr 标识多重字段，在至少有一个字段的值不为 Null 的情况下， Count 函数只计算一个记录。如果全部的指定字段为 Null，没有记录会被计算。使用 （&） 分隔字段名。在查询表达式中可以使用 Count。你可以把这些表达式应用于具备

17、 SQL特性的 QueryDef对象中或在创建基于SQL查询的Recordset时。其他系统环境下的SQL中，有的支持count（distinct/all 列名）的样式，EXCEL2003中的外部导入数据就是使用的JET DB 4.0 SQL ，并不支持count（distinct/all 列名）的用法，只支持count（列名或列名组合表达式）或count（*）的样式。7. Avg 函数计算包含在特定查询字段中的一组数值的算术平均值。Avg（expr）其中 expr 代表一个字符串表达式，它或者标识一个字段，该字段包含要计算平均值的数据；或者标识一个表达式，它用该字段中的数据来执行计算。 ex

18、pr中的运算对象可能包括一个表字段名，一个常数或一个函数（可能是内在的，也可能是用户自定义的，但不是 SQL 合计函数）。使用 Avg 计算的平均值是算术平均值（将全部值的总和除以值的数目）。Avg 函数在计算中不计任何 Null 字段。示例3-1：查询各省份，各类型的平均数量：select 省份,类型,avg（数量） as 平均数量 from 发货清单$ group by 省份,类型示例3-2：统计2007年工程平均数量大于3的各省工程平均数量，并按平均数量降序排列：select 省份,avg（数量） as 平均数量 from 发货清单$ where year（发货日期）=2007 grou

19、p by 省份 having （avg（数量） 3） order by avg（数量） desc8. Min 和 Max 函数 在查询时从一组指定字段的值中返回最小或最大值。Min（expr）Max（expr） expr中的运算对象可能包括一个表字段名，一个常数或一个 函数 （可能是内在的，也可能是用户自定义的，但不是 SQL 合计函数）。说明:可以使用 Min 及 Max 来计算字段中的最小及最大值，该字段是基于指定的合计或分组的。例如，可以使用这些函数返回最低和最高的货运成本。如果没有指定合计，则使用整个表。示例4-1：获得“发货清单”表各省份中的最大发货量：select max（发货量）

20、 as 各省份中的最大发货量 from （select 省份, sum（ 数量） as 发货量 from 发货清单$ group by 省份）示例4-2：获得“发货清单”表各省份中的最小发货量：select min（发货量） as 各省份中的最小发货量 from 9. First 和 Last 函数 在查询所返回的结果集中，第一个记录或最后一个记录所返回的字段值。First（expr）Last（expr）expr 中的运算对象可能包括一个表字段名，一个常数或一个函数（可能是内在的，也可能是用户自定义的，但不是 SQL 合计函数）。First和 Last 函数与 MoveFirst和 MoveL

21、ast法在 DAO 记录集合对象中是相近的。它们只简单返回查询结果集中的第一个或最后一个记录的指定字段值。因为通常返回的记录没有一定的顺序，所以这些函数返回的记录会任意地排列。经本人测试，EXCEL-SQL系统中，带有first或last函数的查询语句，即使查询语句里包含一个 ORDER BY 子句也不起作用，系统还是按原有默认顺序的记录返回第一行或最后一行结果，对此有异议的朋友可以发帖讨论。示例5-1：提取2008年第一笔发货记录的合同号及发货数量：select first（合同号） as 2008年第一笔发货记录的合同号,first（数量） as 发货数量 from 发货清单$ where

22、 year（发货日期） = 2008示例5-2：提取最后发货记录的合同号及发货数量：select last（合同号） as 最后发货的合同号,last（数量） as 发货数量 from 发货清单$【特别说明】下面提到的四个聚合函数（StDev、StDevP、Var、VarP函数）本人均未测试，文中有关这四个函数的内容完全摘自于Microsoft Jet SQL语言参考中的介绍，这里也就没有提供实测示例。请对此有兴趣的朋友自行研究，当然也欢迎分享你的心得体会和研究成果。10. StDev 和 StDevP 函数 返回总体或总体样本的标准偏差的估计值，此估计值用包含在一个查询的指定字段中的一组值来

23、表示。StDev（expr）StDevP（expr）StDevP 函数计算总体，而 StDev 函数则计算总体样本。如果下一级查询中包含两个以下的记录（或没有记录，对于StDevP 函数），这两个函数将返回一个 Null 值（表示不能计算标准偏差）。11. Var 和 VarP 函数 返回一个总体或总体样本的方差的估计值，此估计值用包含在指定查询字段中的一组值来表示。Var（expr）VarP（expr）使用 VarP 函数计算总体，而 Var 函数计算总体样本。如果下一级查询中包含两个以下的记录，Var 及 VarP 函数将返回一个 Null 值，表示不能计算方差。三、 字符串相关函数（一）

24、 字符串查找函数1. Instr函数返回变量型（长型）Variant （Long），指定一字符串在另一字符串中最先出现的位置。InStr（start, string1, string2, compare）由于EXCLE OLE DB中的SQL没有replace类似的函数，所以Instr函数的使用在某些场合显得尤为重要，所以这里就对该函数的各参数做详细说明：start 可选。数值表达式，设置每次搜索的开始位置。如果省略，则从第一个字符位置开始搜索。如果 start 包含 Null，将产生错误。如果指定 compare 参数，则必须指定 start 参数。string1 必选。要在其中进行搜索的字

25、符串表达式。string2 必选。被搜索的字符串表达式。compare 可选。指定字符串比较类型。如果 compare 为 Null，将产生错误。如果省略 compare，则比较类型由 Option Compare 设置决定。请指定有效的 LCID（区域设置 ID），以在比较中使用区域设置特定的规则。compare 参数设置如下： 常量 值 说明vbUseCompareOption -1 使用 Option Compare 语句的设置执行比较。vbBinaryCompare 0 执行二进制比较。vbTextCompare 1 执行文本比较。（此时不区分字母的大小写）vbDatabaseComp

26、are 2 Microsoft Office Access 2007 专用。根据数据库中的信息执行比较。select Instr（XXpXXpXXPXXP, P） as P的位置 - 返回 P的位置值9。在参数compare缺省的情况下，instr（）函数区分大小写。Instr（4,） - 返回 6；Instr（1,w） - 返回 0；上述两个示例都没有from子句。其实，根据我的学习体会，除了聚集函数必须要有from子句之外，本文中提到的其他各类SQL函数都可以不需要from子句，要注意的是，此时该SQL语句查询结果返回值只能是一个。select公司名称, trim（mid（电话,instr

27、（电话,）+1） as 号码 from 供应商$这是一个相对复杂的应用，根据供应商表中原有电话列的 “（区号）号码”的形式，提取各公司的联系电话号码（不要括号和区号，并去掉空格）。2. InStrB 函数作用于包含在字符串中的字节数据。所以 InStrB 返回的是字节位置，而不是字符位置。（二） 字符串转换函数1. CBool（expression） 转换为Boolean型2. CByte（expression） 转换为Byte型3. CCur（expression） 转换为Currency型4. CDate（expression） 转换为Date型5. CDbl（expression） 转换为Double型6. CDec（expression） 转换为Decemal型7. CInt（expression） 转换为Integer型8. CLng（expressio